VSTS4DBP create schema behavior

I recently had to reorganize a database. Basically, I needed create new schemas and to move tables into them. Having a pretty good knowledge of VSVS4DBP, I decided to make usage of its refactoring capability of moving tables to another schema. This article will describe my journey into schema management in VSTS4DBP.

1- The problem : schema creation

The first thing I did was to create a new schema. Pretty strathfoward; I just right clicked under Schema objects –> Database level objects –> Security and selected add new item from the contextual menu. The following dialog box appeared:
 
I named my schema "MySchema" and … nothing! I expected to have the folder created into the solution explorer but the only thing I got was the "MySchema.sql" file that was created in the security folder:

 
I then thought that maybe I was not at the right place to create my schema. I tried to create it from the schema view pane and I ended up with the same result. Still, the newly created schema was visible in the schema view pane though:
 

2- Move a table

I tried then to move a table to the newly created schema. The table has been moved at the right place according to the schema view pane but, still, the folder structure was not created in the solution explorer. And, worst, in the solution explorer the table stayed in its original schema. I could only find its real schema by opening the SQL script as you can see in the following screen captures:

I use refactoring capability of VSTS4DBP to move dbo.ProspetiveBuyer table

The table appears to be moved in schema view pane.

Not in solution explorer though…

3- Deploy the database

I then built and deployed the database project without deploying to the physical database, I simply ask VSTS4DBP to create a SQL script file. From the deployment perspective, everything seemed to be normal. My schema has been created and the table I refactored was in the newly created schema. I then tried to close and reopen solution but it looked like it was impossible to have the folder structure created in either the solution explorer or the windows explorer.

4- Solution: create a new table

I then tried to create a new table in the schema to see where VSTS4DBP would create it. And then voilà! The folder structure got created in windows explorer and appeared in solution explorer.

So here are my conclusions:

1. Adding a new schema does not create the whole structure unless a table is created (not moved) into it.
2. Refactored (moved) tables are only moved in their SQL scripts, not physically in either Solution or Windows explorer. You have to move them to the target folder manually after refactoring.

It is pretty simple once you know it but it took me a lot of time to discover these "features".

Labels: VSDB