Wednesday, July 25, 2007

Change schema for all tables in SQL Server 2005

By using the stored procedure MSforeachtable it's possible to perform the ALTER SCHEMA sql statement for all tables in a given database.
To change the schema for all tables this would look like:

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

Which would look like this if you want to alter schema to dbo:

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

No comments:

Post a Comment