Tuesday, March 27, 2012

Copying/Transferring Tables

Is there a way to copy all tables (not the data, just the structure) and their relationship diagrams from one SQL server db to another?
What is the easiest way?
ThanksRight click on the Database,Generate Sql Script and set the condition on all the tabs.In order to transfer the permissions,roles,relationship you need to go under option in the same wizard.|||IMHO DTS is the best choice here as it will take care of all dependent objects and constraints. In this case you would elect NOT to transfer the data or probably user id and permissions. The only problem with this is it will not transfer the relationship diagram.|||I just want to transfer Tables and diagrams. I don't want other objects.
I know I can use a wizard to transfer the tables but it doesn't have an option to transfer the diagrams.

Originally posted by sqlserver2k
Right click on the Database,Generate Sql Script and set the condition on all the tabs.In order to transfer the permissions,roles,relationship you need to go under option in the same wizard.|||I am not aware of any tool or wizard that will transfer a relationship diagram, however the data is contained in a table called dtProperties and you could use the following to transfer a diagram:

set identity_insert dtproperties on
INSERT INTO <target db>.dbo.dtproperties(id, objectid, property, value, uvalue, lvalue, version)
select * from <source db>.dbo.dtproperties
set identity_insert dtproperties offsql

No comments:

Post a Comment