Thursday, March 21, 2013

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 :-)

No comments:

Scripting out existing database mail configuration

SQL Server Central Artical on Scripting out Existing database mail configuration I wanted to save this here so I can easily find it. Sorry ...