[SOLVED] Add a new table column to specific ordinal position

Add a new table column to specific ordinal position

Is it possible to add a column to a table at a specific ordinal position in 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

Solution

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.