Generate MERGE scripts for all tables in a database

If you have been using the Database Projects (.sqlproj) in Visual Studio 2010 to maintain your database schema in version control, you probably need a good way to store static data for your application in version control as well. There are pre and post deployment scripts to handle that, but one of the cool things about SQL Server 2008 is that it allows for MERGE statements to perform the correct operation on the database side based on the state of the data in your target DB.

This let’s you specify what the static (also called seed, lookup table, and default) data needs to look like in a declarative way!

It’s very powerful, but often times by the time you get around to storing your database in version control, you already have tons of tables with tons of static data in there… and it would be a pain to hand-code all of the SQL MERGE statements. That’s where a stored procedure called sp_generate_merge comes into play. It allows you to generate merge statements for a particular table.

However, I had an entire database full of tables that had static data… and didn’t feel like manually executing that stored procedure on the dozens of tables I had (I also wasn’t sure which ones had static data).

I came up with this quick-and-dirty SQL script to go through all of the tables in my database and generate the MERGE scripts for them. It’s still not perfect since you have to click on all of the links in the result set and copy/paste the content out into your own post-deployment script files.

But, better than doing everything manually:

USE {ENTER DATABASE NAME HERE} GO

DECLARE @tname nvarchar(250), @tschema nvarchar(250) DECLARE table_cursor CURSOR FOR SELECT TABLE_NAME, TABLE_SCHEMA FROM information_schema.tables ORDER BY TABLE_NAME OPEN table_cursor; FETCH NEXT FROM table_cursor;

-- Perform the first fetch FETCH NEXT FROM table_cursor INTO @tname, @tschema

WHILE @@FETCH_STATUS = 0 BEGIN EXEC dbo.sp_generate_merge @tname, @schema=@tschema

FETCH NEXT FROM table_cursor INTO @tname, @tschema END; CLOSE table_cursor; DEALLOCATE table_cursor; GO