Get Relationship Diagram from one db to another?

Get Relationship Diagram from one db to another?

Post by Luther Ananda Mille » Tue, 27 Jun 2000 04:00:00



We are working on a project in which some users are offsite and use the SQL
Server import/export utility to retrieve new tables, views, procs, etc. This
works great, but the relationship diagrams do not get transferred.

Is there a way to copy the relationship diagram from one server/db to
another?

 
 
 

Get Relationship Diagram from one db to another?

Post by Jaime McGeath » Tue, 27 Jun 2000 04:00:00


The diagrams are stored in the dtproperties table of each database, copying
this should work for ya.

jaime


Quote:> We are working on a project in which some users are offsite and use the
SQL
> Server import/export utility to retrieve new tables, views, procs, etc.
This
> works great, but the relationship diagrams do not get transferred.

> Is there a way to copy the relationship diagram from one server/db to
> another?


 
 
 

Get Relationship Diagram from one db to another?

Post by Darren Gree » Tue, 27 Jun 2000 04:00:00




Quote:>We are working on a project in which some users are offsite and use the SQL
>Server import/export utility to retrieve new tables, views, procs, etc. This
>works great, but the relationship diagrams do not get transferred.

>Is there a way to copy the relationship diagram from one server/db to
>another?

Q.  How can I transfer database diagrams?

A.  Database diagrams are stored in the dtproperties table, with each
diagram being made up of several related rows. The primary row has a
property of DtgSchemaOBJECT. The component rows can be identified as
having the primary row's id value in their objectid column.

When the two databases are exactly the same, and the destination does
not have contain any diagrams use the following statement:

SET IDENTITY_INSERT DestinationDB..dtproperties ON

INSERT DestinationDB..dtproperties
SELECT id, objectid, property, value, lvalue, version
FROM SourceDB..dtproperties T1
WHERE EXISTS(SELECT * FROM SourceDB..dtproperties T2 WHERE T2.property =
'DtgSchemaOBJECT' AND T1.objectid = T2.id)

SET IDENTITY_Insert DestinationDB..dtproperties OFF

For SQL 2000 amend the above SELECT statement to include the new uvalue
column.

To transfer between servers use DTS and the DataPump Task, with the
SELECT statement above as your source, and the dtproperties table as
your destination. Remember to set the "Enable Identity Insert" property
of the DataPump to maintain integrity of the data.

For databases that have existing diagrams you will have to transform the
data to ensure you do not try and insert duplicate id values as this is
an IDENTITY column, as well as maintaining consistency of the objectid
values as outlined above.

A slightly different bit of SQL code designed for copying the diagrams
to another server is below :-

-- 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

 In order to move database diagrams from one server to another, the data
from 'dtproperties' needs to be copied over. This will work only if the
database is identical. The data can be exported using a
DataDrivenQueryTask in DTS, BCP or using a linked server setup. The code
sample below can used to move diagrams using linked servers. Please
create the 'dbDiagrams' view in the databases on both servers.

-- [SourceServer] is the name of the linked server &
-- [SourceDb] is the name of the database.
-- Insert only rows with conflicting identity values first.
INSERT dtproperties
SELECT dg2.newobjectid, dg1.property, dg1.value, dg1.lvalue, dg1.version
FROM [SourceServer].[SourceDb].dbo.dbDiagrams dg1 JOIN
(
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 using explicit identity values.
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

===

v2.00 2000.06.13
Applies to SQL Server versions  : 7.0, 2000
FAQ Categories                  : Server Administration and Tools
Related FAQ articles            : diagram2000
Related Microsoft Kb articles   : n/a
Other related information       : n/a
Authors                         : Neil Pike, Darren Green, Umachandar
Jayachandran

--
Darren Green
SQL/DTS - http://www.swynk.com/friends/green/

 
 
 

Get Relationship Diagram from one db to another?

Post by Luther Ananda Mille » Wed, 28 Jun 2000 04:00:00


Thanks!!!


Quote:> Q.  How can I transfer database diagrams?

> A.  Database diagrams are stored in the dtproperties table, with each
> diagram being made up of several related rows. The primary row has a
> property of DtgSchemaOBJECT. The component rows can be identified as
> having the primary row's id value in their objectid column.

...
 
 
 

1. Getting a relationship diagram of a db?

I realize that this is a Sybase specific group, but I'm troubleshooting a
Power Builder 4.0.03 program (on Win NT 4.0) that uses an old, large, local
Watcom database (about 50 tables).  It's getting very hard to get a feel
for the overall database design inside of the Powerbuilder environment.
I'm hoping that someone knows a way to crank out a big relationship diagram
directly from the file.  Perhaps there is a Sybase tool that could do this,
or even some older software available?
-Garr

2. Progress in Colorado

3. Transfer Db Diagrams from multiple Dbs into one Db in SQL 7

4. newbie report problem

5. DB diagram showing a recursive relationship

6. Determining Database Role Membership

7. Transfer DB diagrams and referential integrity relationships

8. Anyone want to assist with the translation of theAdvocacy

9. Advice on DB relationship diagramming S/W

10. one-to-one relationship inserts

11. One-to-one relationships

12. Definining relationships for one-to-one tables in XSD

13. One to one-or-zero relationship