Anyone that uses Visual Studio Database Projects should look into this Extension. It is a diamond in the rough because you won’t find it with all of the other Visual Studio Extensions.
Unless you love to hand craft Data Definition Language (DDL), creating tables in Visual Studio is not fun. You’ll typically create tables in SQL Server Management Studio (SSMS) and do a Schema Compare to reverse engineer those tables from your database into your dbproj. It takes awhile to get proficient at Reverse Engineering, but it works well. Visual Studio Database Projects have their strengths and weaknesses, and this Extension fixes one of the things that used to drive me crazy. When you use reverse engineering, it breaks your table into it’s constituent parts and scatters the pieces into individual folders for: table, primary keys, foreign keys, defaults, etc. The MergeScripts Extension gives you a way to easily bring that DDL back into the table definition itself, so everything is in one place. I find it way easier to manage a table that way, and I think you will too.
I have reverse engineered AdventureWorks2008 here, and displayed how Visual Studio organized the files
Notice that Employee.table.sql is ‘missing’ the PK, FKs, Constraints, and Index definitions. So, if you want to modify any of these, you need to dig through the subdirectories within the Tables folder to find what you are looking for. Using the MergeScripts Extension, it is easy to pull all of the DDL for the Employee table into Employee.table.sql.
BAM! All of the DDL has been merged into Employee.table.sql, and the related files have been deleted from the subdirectories in the Tables folder (in this case, Constraints, Indexes, Keys, and Triggers).
MergeScripts Extension Installation
Visual Studio 2010 is a prerequisite.
- Assuming you have VS2010 installed, download MergeScripts.vsix from https://skydrive.live.com/?cid=9743d44f2931a23d&sc=documents&id=9743D44F2931A23D%211341.
- Then, double click on (or, right click > Open) MergeScripts.vsix.
- Click Install.
- Click Close.
- To be safe, restart Visual Studio if it was open while you were installing.
MergeScripts Extension Usage
This Extension is very straightforward to use:
- Click on the Schema View tab in Visual Studio. If you don’t see it, go to the Visual Studio menu and click on View > Database Schema View.
- Expand the Schemas folder with the Database Schema View.
- Expand the folder of the Schema containing the tables that you want to Merge.
- Right-click the tables > Merge scripts into parent schema object.
Thank you Patrick Sirr!
A special thanks goes out to Patrick Sirr for creating this Extension, and making it available for all of us to use. When I first started using the MergeScripts extension, I found it here: http://blogs.msdn.com/b/psirr/archive/2011/01/05/database-project-extensibility-merge-table-and-view-scripts.aspx