|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Friday, June 26, 2009 8:04 AM
Points: 182,
Visits: 148
|
|
Hi all,
I need to copy all the user talbles from 'dbo' schema to a different schema within the same database.
There are about 260 user tables in 'dbo' with less than 1 GB of data. All these tables are to be copied along with data into this new schema that I created.
This is rather unusual and I never tried anything like this before and there is no business purpose to it. This is just an alternate solution for testing some DDL scripts that I don't want to run on 'dbo' schema tables.
Another test database is not a feasible option for us here.
thanks for any suggestions,
_ub
PS: The 'Script Generator' doesn't generate code including all the options on the tables and syscomments is not of much help either.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, June 23, 2009 9:17 AM
Points: 1,430,
Visits: 1,893
|
|
This is not something I would proudly list in public but....
select 'select * into test.'+name+' from '+name from sysobjects where type = 'U'
Run this in the database and you will get the script you need to execute to create and populate the new tables in their new schema. I have designated the schema to be "test" in this case but you can switch that to whatever you want. You will need to script out all the indexes and apply them to the proper tables.
Hope this gets you started. Enjoy....
David
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, June 26, 2009 8:04 AM
Points: 182,
Visits: 148
|
|
Thanks for the reply David.
The problem, for me, is more with the table definitions than with the data. I know I said 'data too' in the post, but if I could get the DDL part done correctly, I could use your script to get the data copied.
Each table has a lot of PK, FK, BIND DEFAULTS, User Defined Data types, etc...
Though most of these could be scripted out, but not the BIND DEFAULTS. And that is also important.
Sorry for the confusion.
thanks,
_ub
PS: may be once I get the tables created with whatever is possible, then I could BIND the DEFAULTS again with some script.
thanks a lot.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, June 23, 2009 9:17 AM
Points: 1,430,
Visits: 1,893
|
|
I haven't used this but when you generate scripts for the database, (Right click on the database > Tasks > Generate Scripts) there is an option for "Script Bindings" which is default set to "False". Wondering if you were to set that to true and script the database (ensure that other options for indexes, etc are set) if you would get all the DDL that you need including the bindings. You can do a find / replace to replace dbo. with whatever schema name you want after you have the script.
Let me know if this works.
David
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, June 26, 2009 8:04 AM
Points: 182,
Visits: 148
|
|
Thanks again David.
Thats exactly what I tried and it worked.
Unconventional solutions for unconventional situations....
_Ub
|
|
|
|
