;

Using system objects in Visual Studio 2010 database projects

Posted : Tuesday, 18 October 2011 16:05:57

I have recently started using Visual Studio 2010 database projects in order to get a bit of version control and change tracking on the databases I use - invariably these are a flavour of SQL Server (e.g. SQLCE, SQLExpress, SQL Server Standard Edition). The first part of this post is a quick walkthrough of how to get up and running with database projects followed by a minor irritation I’ve come across - I quite often refer to system objects in stored procedures and views, any database projects where this happens will generate warnings when you try to build them, this post outlines how to resolve the problem.

Although its possible to create a database project from scratch, one of the project templates is a database project wizard which allows you to create a database from an existing database:

image

For this post I used a very simple blog database in which there are two tables BlogPost and BlogComments. There is a single foreign-key constraint between BlogPost and BlogComments where the primary key field of the BlogPost table is a foreign key of the BlogComments table. So after selecting “SQL Server 2008 Wizard” from the project template list you get presented with a series of screens where you can choose various options. For the most part I selected the default options except for the following step of the wizard…

non-default

All this means is that the scripts for the various objects types (tables, views etc) will be contained in folders with those names rather than folders named according to the schema to which each object belongs. In my experience of working with databases as an application developer this is how I think of databases – to me schemas are rather an opaque concept, I would expect DBAs to have a different opinion though which is presumably why Microsoft have chosen the default options as they have.

The final step of the wizard is where you get the option to import an existing schema…

image

This will open a pretty standard SQL Server dialog box where you can navigate to a SQL server instance and select a database you wish to import. I selected my SimpleBlog database and the wizard then created my database project, the resultant database project structure is shown below:

image

As you can see, as well as two tables this database project contains a single stored procedure called utils_ListFields, all this does is take a table name and then return a list of fields contained in that table. The SQL is pretty simple so not worth reviewing but is available in the download at the end of this post, the key fact to note is that it queries the system tables. Building the project generates the following warnings in the visual studio error window…

image

As you can see there are a load of unresolved reference warnings. You could suppress these types of warnings in the options for each .sql file but in a typical production database you would want to know about unresolved references so this isn’t really a viable option. It should also be noted that project does build however these unresolved references could indicate missing objects which could result in production errors so ideally you would want to fix them all rather than just ignore them. The problem as it turns out is down to the fact the visual studio database projects do not contain any information about the internal structure of SQL Server databases, specifically the structure of the system objects. Interestingly enough this information is included in Visual Studio but you have to dig about to find it. Looking in the following folder on my machine:

C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\2008\DBSchemas

There are two .dbschema files, master.dbschema and msdb.dbschema. To resolve the warnings in the error window, select the “Add Database Reference…” option in Solution Explorer:

image

…and browse to and select the master.dbschema file then click OK

image

Rebuild the project and all the system object related warnings will disappear.

It seems a bit strange that selecting SQL Server 2008 wizard project template doesn’t actually import the schema for SQL Server 2008 databases but the workaround is straightforward enough. I guess that provided there is enough uptake of these project types then further development will take place on them and these kind of things will be done automatically. I would also assume that other database providers such as Oracle will be added. For now I find these database projects immensely useful, particularly when deploying changes from my development database to a production environment as any differences can be scripted out into a nice concise .sql file that can be reviewed or distributed to a DBA team etc..

  • (This will not appear on the site)