Lately I’ve been utilizing schemas more frequently in my databases, however the first time I did this in VSDBPro was a pain. The reason for this was I didn’t know how to do it, so hopefully this post will save someone else the struggles I went through.
First I want to highlight the differences between Solution Explorer and Schema View. Solution Explorer is the physical file structure, it doesn’t know about relationships other than the folder structure:
Then there is the schema view which does understand the relationships within the database and models very closely the Management Studio layout:
As you would imagine there are pros and cons to both. For example if you have invalid syntax for defining a table, you won’t even see it in schema view, you’ll only see it in solution explorer. However if you have a foreign key relationship that points to a missing column you’ll see it in schema view with the warning symbol. Also the schema view has appropriate icons for different object types.
However back to the issue of schema management. I’m very particular about naming conventions and file placement so not trusting the tool (sorry Gert) I figured I new better and would just go into solution explorer and create the new schema myself. No problem:
And then I tried to create objects under that scheme however the folder structure wasn’t there!! What a pain, so I went out to Windows Explorer copied the file structure from an existing schema, removing all the objects and added them into the project via Solution explorer, all the time wondering about the person who thought this was a good idea.
What’s more is that when I created my first table, it automatically not only created the table sql file, it created the entire folder structure, seen below back in solution explorer: