T-SQL for changing table column order
Once a table is created, the ordinal position of the columns is fixed. This is especially problematic for large data warehouse tables, where new columns are expected to be added over time.
The only solution currently to re-order columns is to drop and recreate the table (or let the SSMS designer do this for you), but this is often not feasible for a large, highly used table, and it cannot be easily scripted.
Please add some ALTER syntax that will allow changing column order.
To maintain alphabetical order when adding a column to a table, it is necessary to provide AFTER Column clause in the ALTER TABLE statement. It's absurd that Microsoft does not provide the ability to set proper ordinal position for a new column added to a table. Dropping and recreating the table means you get a current schema.sys.tables.create_date when all you want is a current schema.sys.tables.modify_date. Not to mention the requirement to add all the data back to the table after the drop / create they suggest.
Steven Hibble commented
Although this would be a nice-to-have metadata operation, I think the workaround is probably all you need. Give your table a view. Select the columns in the order that you want.