Add a new table column to specific ordinal position in Microsoft SQL Server

sql-server database-table change-management
Even Mien picture Even Mien · Apr 20, 2009 · Viewed 92k times · Source

Is it possible to add a column to a table at a specific ordinal position in Microsoft SQL Server?

For instance, our tables always have CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy columns at the "end" of each table definition? I'd like the new column to show up in SSMS above these columns.

If I am scripting all my database changes, is there a way to preserve this order at the end of the table?

FYI, I'm not trying to institute a flame war on if this should even be done. If you want to read about a thread that degenerates quickly into that, here's a good one:

http://www.developersdex.com/sql/message.asp?p=581&r=5014513

Answer

Jose Basilio picture Jose Basilio · Apr 20, 2009

You have to create a temp table that mirrors the original table's schema but with the column order that you want, then copy the contents of the original to temp. Delete the original and rename the temp.

This is what SQL Management Studio does behind the scenes.

With a schema sync tool, you can generate these scripts automatically.

Related questions

How do I get list of all tables in a database using TSQL?

What is the best way to get the names of all of the tables in a specific database on SQL Server?

Read More
Copy tables from one database to another in SQL Server

I have a database called foo and a database called bar. I have a table in foo called tblFoobar that I want to move (data and all) to database bar from database foo. What is the SQL statement to do …

Read More
SQL DROP TABLE foreign key constraint

If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first? GO IF OBJECT_ID('dbo.[Course]','U') …

Read More