Everybody leaves so why wouldn’t you…Changing Schemas with version upgrades

A few months ago I was upgrading from SQL2000 to SQL2005 (it is a slow step in the right direction). The schema had been a user schema and I needed it changed back to the “dbo” schema so it would work with the new software. I first tried recreating the tables and realize it could take me all day. Then I tried the Export data feature. This would also have worked, but again was really time-consuming. Luckily, someone sent me this little script:

exec sp_MSforeachtable "ALTER SCHEMA dbo TRANSFER ? PRINT '? modified' "
Run this on the database you need to fix the schema on and you have just saved yourself a bunch of work.

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s