Magazines, Books and Articles

Friday, July 18, 2008

Copy database diagrams in SQL Server 2005/2008/2012

To copy database diagrams from database dbA to database dbB, run the following in the Query Analyser:

INSERT INTO dbB.dbo.sysdiagrams
SELECT[name],[principal_id],[version],[definition]
FROM dbA.dbo.sysdiagrams

--SELECT * FROM dbB.dbo.sysdiagrams


[Thanks to Pankaj Saha, a colleague at work, for this code snippet.]

Edit: This also works for SQL Server 2008.

Edit: This also works for SQL Server 2012, Service Pack 4.

Edit: Some other links that could be useful:
http://stackoverflow.com/questions/3310137/sql-server-2005-how-to-copy-a-database-diagram-to-another-server
If you get an error "Cannot insert the value null into column "diagram_id"", check this out: http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/767de035-5509-4150-af21-8b6752653f05

8 comments:

Anonymous said...

Great! Was looking for this, and it took me awhile to find it for sql 2005 diagrams. Thank you.

Anonymous said...

But First, if you don't have any Diagrams, table [sysdiagrams] is missing. In order to have this table click on the item [Database Diagram] corresponding to your source Database and SQL Management Studio will make this table for you.

santanu said...

Thank you for this tip.

Steve said...

Thanks for the post.
Steve Boyd

Anonymous said...

:( I get Failure inserting into the read-only column "diagram_id".

santanu said...

Check this out: http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/767de035-5509-4150-af21-8b6752653f05

Shalin Siriwaradhana said...

The question I'm going to ask is not of a relevance here. Could this be represented in a database diagram? Or any other database software to design database diagrams.

Santanu Sen said...

Could you elaborate Shalin? I don't understand your question.

Post a Comment