NAVmoble - the pocket-sized ERP
Optimized for Microsoft Dynamics NAV and Windows Mobile powered devices

Sunday, February 25, 2007

Sql Server 2005: Change schema for all tables

I recently needed to change the schema for all the tables in a Sql Server 2005 database.
Sql Server 2005 provides a T-SQl statement capable to change the schema for a given object called ALTER SCHEMA
You may use the following syntax to change the schema for all the tables in a database. Just change the "new_schema" string with the desired one:

exec sp_MSforeachtable "ALTER SCHEMA new_schema TRANSFER ? PRINT '? modified' "


Enjoy!

18 comments:

Anonymous said...

Thanks a lot! It worked great!
Renato - Brazil

Ruslan Trifonov said...

I'm glad to hear that! :)

Anonymous said...

After "banging my head" one at a time through the first 20 tables...

Did a search found your script, THANKS!!!

U Rock!

Anonymous said...

thanks! this is 100000K helpful!!

:-)

Diesel said...

Man, I've been fighting with this for 2 hours, and this one line fixed the problem in 10 seconds. Thanks!

Lina said...

Nicely done! It worked for me too!

Stitchface. said...

Nice one Ruslan, worked a treat! Many thanks :)

Anonymous said...

Many thanks! That really helped me.
slashi@gmail.com

5pinx said...

Awesome command.

Anonymous said...

Saved me loads of time... thanks

celiker said...

Really, thank you man....

Kash said...

Thank you very much.

gelay said...

Alhamdulillah

this is great! many thanks! very much helpful!

Peeyush Agrawal said...

I get this error:
Msg 15151, Level 16, State 1, Line 1
Cannot transfer the object 'Cost_Of_Package', because it does not exist or you do not have permission.

Any help out there ?

Ignatius said...

Really thank you. It is saved lot of my time

michael said...

yea it's work!!
great :)

Sree said...

It's really helped and also saved lot of time

Thank you very much.

Sean said...

Fantastic. Thank you.