Replacing Schema with new schema of Database objects

Question

Post reply

Replacing Schema with new schema of Database objects

pramod_patnaik

SSC Rookie

Points: 25
More actions
July 1, 2009 at 12:45 pm

#202014

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..

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

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

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 1

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)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 3

Nice one Jack ....

Cheers

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

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 4

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 🙂