Migrating Diagrams in SQL 2000 or older
If you've ever tried to migrate or export database diagrams from one database to another in SQL Server 2000 or older, you may have noticed that no provision is made for doing so within the export wizard. Why the engineers would overlook THAT little detail I have no idea. But I came across a solution and so thought I'd put it out there just in case anybody else ever has the same issue.
Diagrams live in a system table called dtProperties, which is a system table. Every database has this table present, so no need to look in the Master database for that one.
What we're going to do is a basic insert into the dtproperties table of our destination database from our source database, using the following sql:
Bear in mind that the above sql assumes that no other diagrams currently exist in your destination database. Executing the above sql against a dtproperties table that has existing diagrams is a really bad idea since you might be inserting rows with identical IDs.
If you can't see your database's system tables and wish to peruse it, right click on the sql server name in Enterprise Manager, click Edit Server Registration Properties, and make sure the item labeled "show system databases and system objects" is checked, as in the following illustrations:
That's it!
Diagrams live in a system table called dtProperties, which is a system table. Every database has this table present, so no need to look in the Master database for that one.
What we're going to do is a basic insert into the dtproperties table of our destination database from our source database, using the following sql:
SET IDENTITY_INSERT DestinationDB..dtproperties ON
INSERT DestinationDB..dtproperties (id, objectid, property, value, uvalue, lvalue, version)
SELECT T1.id, T1.objectid, T1.property, T1.value, T1.uvalue, T1.lvalue, T1.version
FROM SourceDB..dtproperties T1
SET IDENTITY_Insert DestinationDB..dtproperties OFF
(Note: If the version of SQL server being used is older than version 2000, omit the field named 'uvalue' as it does not exist in previous versions)INSERT DestinationDB..dtproperties (id, objectid, property, value, uvalue, lvalue, version)
SELECT T1.id, T1.objectid, T1.property, T1.value, T1.uvalue, T1.lvalue, T1.version
FROM SourceDB..dtproperties T1
SET IDENTITY_Insert DestinationDB..dtproperties OFF
Bear in mind that the above sql assumes that no other diagrams currently exist in your destination database. Executing the above sql against a dtproperties table that has existing diagrams is a really bad idea since you might be inserting rows with identical IDs.
If you can't see your database's system tables and wish to peruse it, right click on the sql server name in Enterprise Manager, click Edit Server Registration Properties, and make sure the item labeled "show system databases and system objects" is checked, as in the following illustrations:
That's it!
Subscription Options
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
No comments found.