Enterprise Manager Single Diagram Copy Script
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
create procedure dbo.diagram_copy (@source_db varchar(256)='',
@dest_db varchar(256)='',
@diagram varchar(256)='')
as
DECLARE @er integer
DECLARE @sourceid integer
DECLARE @objectid integer
DECLARE @ds nvarchar(800)
DECLARE @pd nvarchar(500)
BEGIN
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
BEGIN TRAN /* Opened a new transaction */ /* Check passed DB variables, quote to prevent injection attack and add .. */ set @source_db=quotename(ltrim(rtrim(isnull(@source_db,''))))+'..'
set @dest_db=quotename(ltrim(rtrim(isnull(@dest_db,''))))+'..'
if ltrim(rtrim(@source_db))='[]..'
begin
set @source_db=''
end
if ltrim(rtrim(@dest_db))='[]..'
begin
set @dest_db=''
end
/* we now should have quoted source db commands, safety for injection code attempts */ set @source_db=@source_db+'dtproperties'
set @dest_db=@dest_db+'dtproperties'
/* full table name has now been established */ if ltrim(rtrim(isnull(@diagram,'')))=''
begin
ROLLBACK
print 'NO DIAGRAM NAME PASSED'
GOTO ERRORBLOCK
end
set @sourceid=0
/* We need to id the primary object id from the schema name row for the diagram name*/ set @ds='select @objid=isnull(objectid,0) from '+ @SOURCE_DB+' where value= '+CHAR(39)+@diagram+CHAR(39)+' and property='+CHAR(39)+'DtgSchemaNAME'+CHAR(39)
set @pd=N'@objid integer OUTPUT'
execute sp_executesql @ds,@pd,@objid=@sourceid OUTPUT
if @sourceid=0
begin
ROLLBACK TRAN
print 'NO DIAGRAM FOUND FOR DIAGRAM NAME = '+upper(@DIAGRAM)
GOTO ERRORBLOCK
end
/* Now test to see if this objects is in the dest db */ set @objectid=0
set @ds='select @objid=isnull(objectid,0) from '+ @DEST_DB+' where value= '+CHAR(39)+@diagram+CHAR(39)+' and property='+CHAR(39)+'DtgSchemaNAME'+CHAR(39)
set @pd='@objid integer OUTPUT'
execute sp_executesql @ds,@pd,@objid=@objectid OUTPUT
if @objectid>0
begin
ROLLBACK tran
print 'DIAGRAM NAME '+upper(@DIAGRAM)+' ALREADY EXISTS IN THE DESTINATION DATABASE!'
GOTO ERRORBLOCK
end
/* Now we get the primary object row from source db and insert it into dest db */
set @ds= 'insert into '+@dest_db +' (objectid,property,value,lvalue,version,uvalue) ' +
' select objectid , property,value,lvalue,version,uvalue from '+ @source_db +
' where objectid='+convert(varchar(20),@sourceid)+' and id= '+convert(varchar(20),@sourceid)
execute sp_executesql @ds
select @er=@@rowcount /* get the rowcount into a variable for safekeeping */ if @er !=1
BEGIN
print convert(varchar(20),@ER)
ROLLBACK TRAN
print 'PROBLEM ON INSERT DUPLICATING DIAGRAM '+upper(@DIAGRAM)
GOTO ERRORBLOCK
end
/* Now we need the id from the row that was just inserted, as it will be the objectid on all the rows, including the row just inserted */ select @objectid=convert(integer,ident_current(@dest_db))
if @objectid<=0
begin
ROLLBACK TRAN
print 'PROBLEM GETTING NEW ID FOR DIAGRAM '+upper(@DIAGRAM)
GOTO ERRORBLOCK
END
set @ds='update '+@dest_db+' set objectid = '+convert(varchar(20),@objectid)+' where id='+convert(varchar(20),@objectid)
execute sp_executesql @ds
/* Now update the row we just processed so that it shows the objectid=id. */ select @er=@@rowcount /* get the rowcount into a variable for safekeeping */ if @er !=1 or @objectid <=0
BEGIN
ROLLBACK TRAN
print 'PROBLEM UPDATING DUPLICATE DIAGRAM '+upper(@DIAGRAM)
GOTO ERRORBLOCK
END
/* Now duplicate the remaining rows from source db */ set @ds= 'insert into '+@dest_db+'(objectid,property,value,lvalue,version,uvalue) '+
'select objectid= '+convert(varchar(20),@objectid)+' , property,value,lvalue,version,uvalue from '+ @source_db+
' where objectid='+convert(varchar(20),@sourceid)+' and id!='+ convert(varchar(20),@sourceid)
/* Now we need the id from the row that was just inserted, as it will be the objectid on all the rows, including the row just inserted */ execute sp_executesql @ds
select @er=@@rowcount /* get the rowcount into a variable for safekeeping */ if @er <= 0
BEGIN
ROLLBACK TRAN
print 'PROBLEM DUPLICATING DETAILS FOR DIAGRAM '+upper(@DIAGRAM)
GOTO ERRORBLOCK
END
print 'DIAGRAM '+upper(@diagram)+' WAS ADDED TO '+upper(@source_db)+' AS OBJECT ID '+convert(varchar(20),@objectid)
COMMIT TRAN /* close the transaction out */ SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
RETURN
ERRORBLOCK:
begin
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
RETURN
end
END
END 
