Replacing Schema with new schema of Database objects

  • Hi,

    I have a schema called "Emp" and all the tables binds to this schema. Now I have created a new schema called "Employee" and wants to bind this new schema "Employee" instead of "Emp". How can I do this with minimal effort.

    Also old schema "Emp" is used in my stored procedure and views with tables. I also wants to replace in those stored proc and views.Is there any good script/tool is available for this.

    Thanks..

  • This is your answer my friend 🙂

    sp_msforeachtable 'ALTER SCHEMA test TRANSFER ?'

    this will change the schema to test .You can change it to employee.

    make sure you are in the right database context.

    Regards

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • Whenever I recommend using sp_MSForEachTable I always include a link to this article which explains the parameters of the procedure and how it works.

    I would script out all my procedures, functions, and views and then do a search and replace to change Emp. to Employee.

  • Nice one Jack ....

    Cheers

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • Hi,

    Try this:

    set the o/p mode to text in SSMS .. then run the following .....

    SELECT 'ALTER SCHEMA Employee TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME

    FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'EMP'

    sp_msforeachtable

    this is an undocumented procedure ... it is very handy but not recommended by Microsoft.

    Also old schema "Emp" is used in my stored procedure and views with tables. I also wants to replace in those stored proc and views.Is there any good script/tool is available for this.

    I would go with what Jack suggested ....

    I would script out all my procedures, functions, and views and then do a search and replace to change Emp. to Employee.

    Hope this helps ... 🙂

    Happy 4th of JULY !

    Thanks,

    \\K

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

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

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