SELECT *
FROM dtproperties d1
WHERE EXISTS(SELECT * FROM dtproperties d2
                              WHERE d2.property = 'DtgSchemaOBJECT' AND
                                            d1.objectid = d2.id)

-- View to simplify diagrams import & export.
-- This can be created in the model database so that it will
-- present in all newly created databases.
CREATE VIEW dbDiagrams
AS
SELECT id, objectid, property, value, lvalue, version
FROM dtproperties d1
WHERE EXISTS(SELECT * FROM dtproperties d2
                              WHERE d2.property = 'DtgSchemaOBJECT' AND
                                            d1.objectid = d2.id)
go

-- The code snippet below can be used to import diagrams from
-- one server to another using linked server setup.

-- Insert only rows with conflicting identity values first.
set identity_insert dtproperties off
insert dtproperties
SELECT dg2.newobjectid, dg1.property, dg1.value, dg1.lvalue, dg1.version
FROM [SourceServer].[SourceDb].dbo.dbDiagrams dg1 JOIN
(
-- This query generates the next identity value for each diagram.
SELECT d1.objectid, (SELECT MAX(id) FROM dtproperties d3) +
                        (SELECT COUNT(*) FROM dbDiagrams d4
                         WHERE d4.objectid <= d2.id And
                                d4.property = 'DtgSchemaOBJECT') AS newobjectid
FROM [SourceServer].[SourceDb].dbo.dbDiagrams d1 JOIN dbDiagrams d2
ON d1.objectid = d2.id
WHERE d1.property = 'DtgSchemaOBJECT' And d2.property = 'DtgSchemaOBJECT' 
) AS dg2
ON dg1.objectid = dg2.objectid

-- Insert the rest now.
set identity_insert dtproperties on
insert dtproperties ( id, objectid, property, value, lvalue, version)
SELECT id, objectid, property, value, lvalue, version
FROM [SourceServer].[SourceDb].dbo.dbDiagrams d1
WHERE NOT EXISTS(SELECT * FROM dbDiagrams d2
                 WHERE d2.property = 'DtgSchemaOBJECT' AND
                       d1.objectid = d2.id)
set identity_insert dtproperties off

This page was last updated on May 01, 2006 04:28 PM.