For some, working with Databases is fun and for others, it could possibly be a nightmare. Understanding the structure of a Database schema from the available documentation could spell doom for many developers.
A picture is worth a thousand words. This golden saying applies aptly to Databases! Yes, often, a pictorial representation of a Database conveys more meaning about the Database Schema than the SQL scripts generated or other documentation that may be available. One of the first thing developers wants to do when starting work on a new project is to understand the Database schema. Sometimes, understanding a complex database schema can be simplified largely by a relationship diagram.
The Database diagram tools in the SQL Server enable administrators and developers to create Database diagrams very easily. This article demonstrates an efficient way to move DB diagrams from one Database to another.
We are going to use two sample Databases for this purpose. They are named PlayDB and PlayDBMirror for sake of simplicity and clarity. Let us create some sample tables and one sample diagram called “sample1” in the Database PlayDB. The objective is to copy the diagrams from this database to PlayDBMirror. Let us see how this can be accomplished.
Note: It is not necessarily that you need to create a DB called PlayDB in your server. You can also work with Northwind or Pubs database. The bottom line is don’t use the production server to test these out.
Run through the steps from 1 to 8:
1. Create a new Database by right clicking on "Databases" in Enterprise Manager and name it as "PlayDBMirror".
2. Now right click on your source Database (for me it is "PlayDB")
3. Choose "All Task" >> "Export Data" and choose the source and destination DB.
4. In "Specify Table Copy or Query screen" choose the second option "Use a query to specify the data to transfer" and click next.
5. In the next screen type the following query "select * from dtproperties" (Note: this would try and push ALL records from source dtproperties to destination dtproperties)
6. In "Select Source Tables and Views" click on the "Results" under heading destination, and then manually change it to dtproperties.
7. In the next screen choose "Run Immediately" check box and click on Next.
8. That’s it click on "Finish" on the last screen.
Now, we have successfully copied all the database diagrams from PlayDB to PlayDBMirror. Let us suppose we have somehow modified the database diagrams and want to move them again. Running through the steps from 1 to 8 will accomplish the task and lo here is the catch. If you check the diagrams section in the destination database (PlayDBMirror), we see the diagram sample1 copied successfully.
The internals
What happens internally is that the diagrams are stored in a table called dtproperties.
Each diagram internally has 7 rows in “dtproperties” with unique “objectid”. They are listed below for the purpose of completion:
1. DtgSchemaOBJECT
2. DtgSchemaGUID
3. DtgSchemaNAME
4. DtgDSRefBYTES
5. DtgDSRefDATA
6. DtgSchemaBYTES
7. DtgSchemaDATA
Excuse me, did I say unique objected?! Now here is where you need to pay attention. If this Database is queried, you will find that there are 14 rows!! Which means there are two diagrams now! How strange? The records were appended to the table during the second copy operation and were not overwritten! Why? And guess what, all of them have the same object id. Which means, for one diagram, there are 14 rows in the table dtproperties. In other words, there are two unique object Ids.
Let us analyze and figure what would happen if two diagrams have the same ID.
1. Double click on any one of the diagrams
2. Make some changes to it, save and close it.
3. Open the other diagram the same changes would reflect there also. Similarly if you drop one diagram the other one would also get delete. Believe me!! Rarely it has thrown some errors also for me in this step. And I have been left with no other alternative than to delete both diagrams.
The solution using Query analyzer
To move all the existing diagrams, we do this:
Insert into dtproperties
(
objectid,
property,
value,
uvalue,
lvalue,
version
)
Select
objectid,
property,
value,
uvalue,
lvalue,
version
From
PlayDB..dtproperties
Where
value not in (select value from dtproperties)
This query when run from the Query analyzer of the target Database ensures that all the diagrams are copied to the target Database but ensures that if the target Database already has a diagram with the same name, it doesn’t get copied.
Let us try to copy selected diagrams from the source to the target Database.
First, to decide on the diagrams that you want to copy, run the query
Select objectid, property, value from playdb..dtproperties
In the output of this query look in the "property" column for "DtgSchemaNAME" value. The DtgSchemaNAME property would have the name of the diagram; use this in conjunction with the objectid column to locate the diagram you would like to transfer.
We should also check the dtproperties table of destination Database before transfer. Using the above query, check that the destination dtproperties does not already have the objectid, which you are going to transfer now. If it does, get the maximum id from the destination table and use it within your select statement. Use that max value in the below query to be 100% sure its unique within the dtproperties table in the destination Database. For the sake of discussion let us use an arbitrary number 100 as my objectid.
Insert into dtproperties --- this is the table in the destination db.
(
objectid,
property,
value,
uvalue,
lvalue,
version
)
Select
100, -- Object ID
property,
value,
uvalue,
lvalue,
version
From
PlayDB..dtproperties
Where
objectid = 1 --- Replace this number with the object ID of the diagram which you want to transfer
Conclusion
Backing up the source database and restoring it in the destination database can accomplish the same task. But the crux of the matter is copying specific diagrams from one Database to another. Happy programming!
Technorati tags: Databases, SQL Server
A picture is worth a thousand words. This golden saying applies aptly to Databases! Yes, often, a pictorial representation of a Database conveys more meaning about the Database Schema than the SQL scripts generated or other documentation that may be available. One of the first thing developers wants to do when starting work on a new project is to understand the Database schema. Sometimes, understanding a complex database schema can be simplified largely by a relationship diagram.
The Database diagram tools in the SQL Server enable administrators and developers to create Database diagrams very easily. This article demonstrates an efficient way to move DB diagrams from one Database to another.
We are going to use two sample Databases for this purpose. They are named PlayDB and PlayDBMirror for sake of simplicity and clarity. Let us create some sample tables and one sample diagram called “sample1” in the Database PlayDB. The objective is to copy the diagrams from this database to PlayDBMirror. Let us see how this can be accomplished.
Note: It is not necessarily that you need to create a DB called PlayDB in your server. You can also work with Northwind or Pubs database. The bottom line is don’t use the production server to test these out.
Run through the steps from 1 to 8:
1. Create a new Database by right clicking on "Databases" in Enterprise Manager and name it as "PlayDBMirror".
2. Now right click on your source Database (for me it is "PlayDB")
3. Choose "All Task" >> "Export Data" and choose the source and destination DB.
4. In "Specify Table Copy or Query screen" choose the second option "Use a query to specify the data to transfer" and click next.
5. In the next screen type the following query "select * from dtproperties" (Note: this would try and push ALL records from source dtproperties to destination dtproperties)
6. In "Select Source Tables and Views" click on the "Results" under heading destination, and then manually change it to dtproperties.
7. In the next screen choose "Run Immediately" check box and click on Next.
8. That’s it click on "Finish" on the last screen.
Now, we have successfully copied all the database diagrams from PlayDB to PlayDBMirror. Let us suppose we have somehow modified the database diagrams and want to move them again. Running through the steps from 1 to 8 will accomplish the task and lo here is the catch. If you check the diagrams section in the destination database (PlayDBMirror), we see the diagram sample1 copied successfully.
The internals
What happens internally is that the diagrams are stored in a table called dtproperties.
Each diagram internally has 7 rows in “dtproperties” with unique “objectid”. They are listed below for the purpose of completion:
1. DtgSchemaOBJECT
2. DtgSchemaGUID
3. DtgSchemaNAME
4. DtgDSRefBYTES
5. DtgDSRefDATA
6. DtgSchemaBYTES
7. DtgSchemaDATA
Excuse me, did I say unique objected?! Now here is where you need to pay attention. If this Database is queried, you will find that there are 14 rows!! Which means there are two diagrams now! How strange? The records were appended to the table during the second copy operation and were not overwritten! Why? And guess what, all of them have the same object id. Which means, for one diagram, there are 14 rows in the table dtproperties. In other words, there are two unique object Ids.
Let us analyze and figure what would happen if two diagrams have the same ID.
1. Double click on any one of the diagrams
2. Make some changes to it, save and close it.
3. Open the other diagram the same changes would reflect there also. Similarly if you drop one diagram the other one would also get delete. Believe me!! Rarely it has thrown some errors also for me in this step. And I have been left with no other alternative than to delete both diagrams.
The solution using Query analyzer
To move all the existing diagrams, we do this:
Insert into dtproperties
(
objectid,
property,
value,
uvalue,
lvalue,
version
)
Select
objectid,
property,
value,
uvalue,
lvalue,
version
From
PlayDB..dtproperties
Where
value not in (select value from dtproperties)
This query when run from the Query analyzer of the target Database ensures that all the diagrams are copied to the target Database but ensures that if the target Database already has a diagram with the same name, it doesn’t get copied.
Let us try to copy selected diagrams from the source to the target Database.
First, to decide on the diagrams that you want to copy, run the query
Select objectid, property, value from playdb..dtproperties
In the output of this query look in the "property" column for "DtgSchemaNAME" value. The DtgSchemaNAME property would have the name of the diagram; use this in conjunction with the objectid column to locate the diagram you would like to transfer.
We should also check the dtproperties table of destination Database before transfer. Using the above query, check that the destination dtproperties does not already have the objectid, which you are going to transfer now. If it does, get the maximum id from the destination table and use it within your select statement. Use that max value in the below query to be 100% sure its unique within the dtproperties table in the destination Database. For the sake of discussion let us use an arbitrary number 100 as my objectid.
Insert into dtproperties --- this is the table in the destination db.
(
objectid,
property,
value,
uvalue,
lvalue,
version
)
Select
100, -- Object ID
property,
value,
uvalue,
lvalue,
version
From
PlayDB..dtproperties
Where
objectid = 1 --- Replace this number with the object ID of the diagram which you want to transfer
Conclusion
Backing up the source database and restoring it in the destination database can accomplish the same task. But the crux of the matter is copying specific diagrams from one Database to another. Happy programming!
Technorati tags: Databases, SQL Server
Comments