Skip to main content

Posts

Showing posts from December, 2010

MS SQL: How to change the table schema

Honestly I am not a DBA and it scares me to dead, when I have to write or run MS SQL administrative level scripts. I was having too much issue with a production backup and restore because some tables were user schema based and each time on staging; I had to delete and recreate tables with right schema. Today, I finally decided to change the schema of three tables which were giving me headaches on the live site. As the tables were created with a user name schema; I couldn't delete the tables as they have live data nor I knew how to change the schema.

After finding a solution on MSDN, I decided to modify and run it on the production server after some testing and review on staging. Below is the script that I have used. It is just a one-liner but it removes the hassle that was giving me too much grief over permissions and ownership.

ALTER SCHEMA MyNewSchema TRANSFER oldSchema.TheTableName

Happy Coding!