As per http://msdn.microsoft.com/en-us/library/aa337556.aspx
This task cannot be performed using Transact-SQL statements.
Well, it can be, using
create/ copy /
drop/ rename, as answered by komma8.komma1
Or you can use SQL Server Management Studio
- In Object Explorer, right-click the table with columns you want to reorder and click Design (Modify in ver. 2005 SP1 or earlier)
- Select the box to the left of the column name that you want to reorder. (You can select multiple columns by holding the [shift] or
the [ctrl] keys on your keyboard.)
- Drag the column(s) to another location within the table.
Then click save. This method actually drops and recreates the table, so some errors might occur.
If Change Tracking option is enabled for the database and the table, you shouldn't use this method.
If it is disabled, the Prevent saving changes that require the table re-creation option should be cleared in Tools menu > Options > Designers, otherwise "Saving changes is not permitted" error will occur.
- Disabling the Prevent saving changes that require the table re-creation option is strongly advised against by Microsoft, as it leads to the existing change tracking information being deleted when the table is re-created, so you should never disable this option if Change Tracking is enabled!
Problems may also arise during primary and foreign key creation.
If any of the above errors occurs, saving fails which leaves you with the original column order.