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!

Published Sunday, February 25, 2007 6:53 AM by xman892
Filed under:

Comments

# re: Sql Server 2005: Change schema for all tables

Sunday, February 25, 2007 7:22 AM by mosessaur

Thanks for the tip. I'm sure I might need that sooner or later

# re: Sql Server 2005: Change schema for all tables

Wednesday, May 09, 2007 4:19 AM by barak

greate! And is there somthing like this for all db views ?

# re: Sql Server 2005: Change schema for all tables

Tuesday, June 12, 2007 1:53 PM by Zunhun

Worked like magic :-) Many thanks!

# re: Sql Server 2005: Change schema for all tables

Friday, August 03, 2007 2:05 PM by mldeterm

Thanks so much!  Exactly what I needed.

# re: Sql Server 2005: Change schema for all tables

Monday, August 31, 2009 3:04 AM by vikas

Thanks a lot...its what i need.