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

Question

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 1

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.

Answer 2

go into SQL Server management Studio, and "design" an existing table. Insert a column in the middle, right click in an empty area and select Generate Change Script...

Now look at the script it creates. it will basically create a temp table with the proper column order, insert the data from the original table, drop the original table, and rename the temp table. This is probably what you'll need to do.

You may also need to uncheck this option to allow creation of change scripts