SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Enterprise Manager Single Diagram Copy Script

By Brian Risley,

This procedure will COPY an Enterprise Manager SINGLE DIAGRAM object between databases on a given SQL Server.
This procedure takes in a source DB name, a Dest DB name, and the Diagram name.

(Exec diagram_copy 'SOURCE_DB', 'DEST_DB', 'DIAGRAM NAME')

This procedure DOES NOT have to be placed in either of the databases, you may run it from a connection to any database on the server where the script exists.
You need to have rights to the DTPROPERTIES table and it must exist. If it currently does not exist on your destination DB, you should go into EM and save a simple diagram out to create the table.

To transfer information between servers, create a new DB and setup DTPROPERTIES and copy all the diagrams to this DB. Then backup this DB and restore it to your destination server and then run this procedure there.
It would be a simple task to front end this procedure to a process that supplied the diagram names for mass movement.

The following query will return the diagram names from the current DB.
select objectid,value from dtproperties where property='DtgSchemaNAME'

This can be placed into a procedure or view to make things easier.

GENERAL INFO: Each diagram object is composed of multiple rows in the dtproperties table. The following property values are used:
DtgSchemaOBJECT - Is the primary ID for the object. All rows use this record's ID for objectid.
DtgSchemaGUID - Not sure how this is used, All my diagrams have the same GUID.
DtgSchemaNAME - Actual name of the diagram
The Bytes record contains the datalength of the corresponding data records's lvalue image field.

Keep in mind that tables must be common between the databases. If relationships are different, the links may not appear the same as the source diagram. Displayed Link Line positions may move.

Tables may have differences, but if named the same

Total article views: 170 | Views in the last 30 days: 1
Related Articles

SQL Server Database diagram

Copy Database diagram in SQL Server with Relationship


Database Modeling and Diagrams with SQL Server 2008

How to use Database Diagram tool in SQL SERVER.


Database diagrams

Database diagrams



Hi all What is the "dtproperties" system table? when quering sysobjects this table has an xtype o...


Database Diagrams

DB Diagrams in SQL Server 2000/2005