Post reply

How could I copy several tables from one owner to other in the same database

  • May 11, 2004 at 9:49 am

    #65181

    How could I copy several tables from one owner to other in the same database ?????

    I have a database with around 150 tables with constraints checking several relationships, indexes, etc. (but without store procedures)

    I want to copy all of them to other owner (that I already have), in this case the destination owner doesn't have any table.

    I want to copy all of them to other owner (that I already have), in this case the destination owner has the same tables that the source owner has. (I only want to update the information in all tables.)

    What can I do if the all users are in the same database, and I can NOT create another (This is a lack of funcionality of my ERP.)?

  • May 11, 2004 at 4:58 pm

    #506057

    script the objects out from the source, search/replace, oldwoner. with newowner.

  • May 12, 2004 at 1:23 am

    #506079

    If You only want to change the owner of the tables, take a look at:

    sp_changeobjectowner

    ( Also read the Remarks in BOL for this proc, concerning permissions for the changed objects(tables in Your case)&nbsp

    /rockmoose


    You must unlearn what You have learnt

  • May 12, 2004 at 5:09 am

    #506111

    sp_changeobjectowner

    will work if the person wants to change the owner of the object but it seems that in this case its required to keep the object with original owner as well.

     

    Indu

    Best Regards,

    Indu Jakhar

  • May 12, 2004 at 10:46 am

    #506176

    Ok everybody . . . Newbie has the reason . . . I don't want to change the owner, I want to do two processes

    -- First case:

    Copy objects from one users to other (the destination user doesn't have any object) in the same database.

    -- Second case:

    Update all the information from one user to other (the destination user have the same objects as the source) in the same database.

    I apreciate your help . . . .

  • May 12, 2004 at 1:39 pm

    #506203

    Ok,

    -- First case, generate same objects with new owner

    Use Steve Jones script + search and replace method. Just make sure that in QA -> Tools -> Options -> Script You check "Qualify object name with owner".

    -- To copy data...

    To copy the data from original tables to the new ones You could use DTS, T-SQL, bcp...

    Also there are som scripts on this forum that will generate insert scripts for You.

    Take a look at

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=112062

    or

    http://www.karaszi.com/sqlserver/info_generate_script.asp

    /rockmoose


    You must unlearn what You have learnt

Viewing 6 posts - 1 through 6 (of 6 total)

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