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

at

8 comments:

  1. AnonymousMarch 20, 2009 at 2:49 AM

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

    ReplyDelete
  2. AnonymousJuly 2, 2009 at 5:55 PM

    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.

    ReplyDelete
  3. santanuJuly 3, 2009 at 12:26 AM

    Thank you for this tip.

    ReplyDelete
  4. UnknownJuly 13, 2010 at 9:54 PM

    Thanks for the post.
    Steve Boyd

    ReplyDelete
  5. AnonymousSeptember 4, 2010 at 8:42 PM

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

    ReplyDelete
  6. santanuSeptember 5, 2010 at 10:12 AM

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

    ReplyDelete
  7. EvanNovember 18, 2014 at 3:17 PM

    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.

    ReplyDelete
  8. santanuNovember 18, 2014 at 5:20 PM

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

    ReplyDelete