Renaming Objects in Visual Studio Database Projects

Just so you know, if you want to rename a table, column, etc. in a Visual Studio database project, don’t just change the object name in the definition file.  Instead, right click on the object and choose Refactor→Rename. This will (1) rename it everywhere else it is used (good!), and (2) make sure the deployment script that is eventually generated knows that the table or column was renamed so that it will rename it instead of dropping and recreating it (double plus extra good!).

I just tried this: changing the datatype of a column I renamed on a table I renamed; the resulting deployment script renamed the table, then renamed the column, and then altered the column to the new datatype. Just what I wanted!

References to Other Databases in Visual Studio Database Projects

We recently decided to move our maintenance of our database schemas from a pile-of-scripts to Visual Studio 2010's database projects, so I created a database project and imported its objects from our existing Development database.  The resulting project had thousands of errors and warnings.

The significant majority of the errors were due to cross-database references in stored procedures and functions. Here is how we resolved them:

1. The first type of error was a reference to a project that I intended to bring into our solution anyway. I created a new project in the same solution and then created a database reference from the first database to the second. Since they were on the same server, and since we, at this time, have no intention of changing the database names, I just set the database name to a literal in the reference options. This obviated my having to replace references to the database with a variable.
   
   
2. The second type of error was for a database that we have no control over and that we had no intention of including in our solution. To solve this problem, we had to create a .dbschema file. To do this, we used the vsdbcmd command line tool.
   
   The vsdbcmd command line tool resides in C:\Program Files\Microsoft Visual Studio 10.0\VSTSDB\Deploy\ on a 32-bit Windows XP system, and the syntax to generate the .dbschema file is as follows:
   

   "C:\Program Files\Microsoft Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd" /Action:Import /ConnectionString:"Data Source=«server name»;Initial Catalog=«database name»;Integrated Security=True" /DatabaseSchemaProvider:SQL /ModelFile:«output filename».dbschema

   Once the .dbschema file is created, a database reference was set up to reference the file.
   

These steps eliminated about half the errors, but there were still about 1,500 remaining. Some of these were the result of obsolete stored procedures and functions referencing obsolete object. These could safely be removed from the project, since they clearly were not being used.

Another class of errors was when an object referenced another object in the same database with the full three-part object name (ie., database name, schema, object name).  Visual Studio complains about this because it allows you to rename the database at deployment time. However, if your scripts have hard-code the database name, this will cause problems. The solution to this is simply to remove the database name from the object reference.

Finally, I discovered that I had references in database 1 to objects in database 2, and vice versa. Visual Studio does not permit database 1 to reference database 2 and database 2 reference database 1.  It considers this a circular reference, even though it is different objects in each database referencing and being referenced by the other database.  The solution we used for this was to suppress certain warnings at the file level.  To do this, right-click on the file in the Solution Explorer and choose “Properties” (or press Alt-Enter) and set the value of the “Suppress Warnings” property to the warning number (or numbers, comma-separated) you wish to ignore.