Post reply

Copy tables with data into a schema in same DB

  • November 14, 2008 at 2:29 pm

    #132089

    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.

    SqlTimes Blog on Sql Server

  • November 14, 2008 at 2:43 pm

    #898892

    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

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • November 14, 2008 at 3:09 pm

    #898904

    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.

    SqlTimes Blog on Sql Server

  • November 14, 2008 at 3:31 pm

    #898910

    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

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • November 17, 2008 at 8:27 am

    #899364

    Thanks again David.

    Thats exactly what I tried and it worked.

    Unconventional solutions for unconventional situations....

    _Ub

    SqlTimes Blog on Sql Server

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply