Post reply

Stored Procedure to transfer all DB objects including data from one schema to another schema

  • December 9, 2016 at 1:24 am

    #1916968

    I think you might need to get someone in to help you with this. Your requirement is changing with every new post, and you're making up syntax and hoping that it will be valid. There's no such construct as ALTER SCHEMA... COPY.

    Eric suggested using the Import/Export wizard, but I'm not sure that works for stored procedure definitions. If you only need to do this as a one-off, click on the Tables node under your database ins SSMS and press F7 to open Object Explorer Details. Select all tables (or just the ones you want if there are tables in more than one schema), right-click and choose Script Table as -> CREATE To -> New Query Editor window. Do the same for views, functions, stored procedures and anything else you want to copy. Now edit the scripts so that the objects are created in the new schema, and run them one by one. You'll have to be careful if there are any dependencies (foreign key constraints, for example). If you need the data also, use the Import/Export wizard to copy that over.

    If you need to do this regularly (and I can't think of a good reason why you would) then I think SSIS has some specialised tasks that do this sort of thing - you can write a package.

    Good luck

    John

  • December 9, 2016 at 2:07 am

    #1916977

    GA_SQL (12/9/2016)

    i got the requirement exactly.. i have schema called dbo.table1,dbo.table2 that has to be copied (not transfer) dbonew.table1,dbonew.table2 (with in same db), this below script transfers the name since dbo has been replaced to dbo1, please help me in copy one schema to another schema with in same DB

    If you want to do this in T-SQL/Stored Procedure, then you have to somehow script the object, create them in the destination schema and then insert the data. As I said before, T-SQL is not the best way of doing this. Again, do you have any other options (id. PoSh, SSIS etc.)?

    😎

    Are there any constraints, relationships etc. that needs to be copied with the tables?

Viewing 2 posts - 16 through 16 (of 16 total)

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