Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved 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
DtgDSRefBYTES
DtgDSRefDATA
DtgSchemaBYTES
DtgSchemaDATA
The Bytes record contains the datalength of the corresponding data records's lvalue image field.

IMPORTANT!
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: 151 | Views in the last 30 days: 1
 
Related Articles
FORUM

SQL Server Database diagram

Copy Database diagram in SQL Server with Relationship

ARTICLE

Database Modeling and Diagrams with SQL Server 2008

How to use Database Diagram tool in SQL SERVER.

FORUM

Database diagrams

Database diagrams

FORUM

Database Diagrams

DB Diagrams in SQL Server 2000/2005

FORUM

dtproperties

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

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones