Altering schema for all tables in MS SQL Server 2005

I ran into a situation today where I needed to alter all tables for an MS SQL Server 2005 database.
A quick Google revealed this post from Ruslan Trifonov.

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

Excellent :-)  That was easy ...

Reference to ALTER SCHEMA command is here.
In it's simplest form, the command appears to take this shape: ALTER SCHEMA HumanResources TRANSFER Person.Address;

For those who aren't familiar with the '?' in T/SQL, it's represents a parameter, so this statement is parameterized.  Essentially, sp_MSforeachtable is going to pass the name of each table to statement/query.

Great stuff. Big thanks to Ruslan. That was easy :-)

Comments

Popular posts from this blog

Detecting Transaction Isolation Level through Profiler Trace and other means on MS SQL Server 2005

Regular Expressions in SQL Server, DB2 UDB, and Oracle