Deborah's Developer MindScape

The new Visual Studio 2010 Database Project, as described in this prior post, makes it easy to work with scripts for an existing database directly within Visual Studio.

You can automatically build scripts for your existing tables, stored procedures, indexes, constraints, and so on. You can then check these scripts into and out of source code control. You can edit the scripts to change the table schemas or stored procedures. You can then build the project to validate the scripts and then deploy the scripts back to the database.

Start by adding a VS 2010 Database project to your solution following the steps in this prior post. Then follow the steps below to create the scripts in the project from an existing database.

1. Right-click on the Database project name in Solution Explorer and select Import Database Objects and Settings.

The Import Database Wizard is displayed.

2. Select the database connection to the existing database, any desired properties, and click on the Start button to start the wizard.

The database objects are then scripted directly into your Database project:

3. Click on the Finish button to finish.

View the Database project in Solution Explorer to see your added database objects.

Notice that this process added the three tables and three stored procedures from my database.

You can then check these files in and out of source code control just like any other project files. You can double-click on any script to edit it.

When you are ready to apply the changes to your development database, you can deploy the scripts as detailed in this post.

NOTE: If your database has been around a while, it is possible that some of the stored procedures may have gotten out of synchronization with the tables. For example, if your DBA renamed the CustomerInfo table to Customer, it is possible that your associated stored procedures were not all modified to match. Unless your code calls all of the stored procedures, you may not have noticed the problem. Since the Database project is "alive", once you follow this process to create the associated scripts into your Database project, any mismatches are defined as application warnings:

This helps you ensure that your stored procedures and tables are always in synchronization.

If you want these types of warnings to be treated as application errors and prevent execution of the application from within Visual Studio, you can change these warnings to errors. Double-click on the Properties under the Database project in Solution Explorer and click on the Build tab. Then check the "Treat Warnings as Errors" checkbox. Your warnings will immediately become application errors.

Use the techniques detailed here any time you are working with an existing database and want to add the scripts to your solution.

Enjoy!