How to change column order in a table using sql query in sql server 2005?

In SQLServer Management Studio:

Tools -> Options -> Designers -> Table and Database Designers

Unselect Prevent saving changes that require table re-creation.

Now you can reorder the table.

If you have not yet added any data into your table yet, there is one way to move the columns around. Try this:

1. In SSMS, click Tools > Options > Designers > Table and Database Designers > Uncheck the box next to Prevent saving changes that require table re-creation > Click OK.
2. In the object tree, right-click on your table and select Design > in the thin column to the left of the Column Name column, you can click and drag the columns around to wherever you want them. When you're done, just go to close the Design tab and SSMS will ask you if you want to save your changes, click OK.

Optional: 3. Re-enable the checkbox for the option from Step 1 to re-secure your table.

Hope this helps someone!

Credit goes to Microsoft: https://docs.microsoft.com/en-us/troubleshoot/sql/ssms/error-when-you-save-table#more-information

Not sure if still relevant, but SSMS can generate a change scripts for this.

1. Re-order (drag the column) the table in Designer View
2. Click on 'Generate Change Script'

The generated script contains the script which does the following:

1. Create a temporary table
2. Adds the constraints, relationships and triggers from original table to temporary table
3. Drop original table
4. Rename temporary table to original table name

If the columns to be reordered have recently been created and are empty, then the columns can be deleted and re-added in the correct order.

This happened to me, extending a database manually to add new functionality, and I had missed a column out, and when I added it, the sequence was incorrect.

After finding no adequate solution here I simply corrected the table using the following kind of commands.

ALTER TABLE  tablename  DROP COLUMN  columnname; 
ALTER TABLE  tablename  ADD columnname columntype;

Note: only do this if you don't have data in the columns you are dropping.

People have said that column order does not matter. I regularly use SQL Server Management Studio "generate scripts" to create a text version of a database's schema. To effectively version control these scripts (git) and to compare them (WinMerge), it is imperative that the output from compatible databases is the same, and the differences highlighted are genuine database differences.

Column order does matter; but just to some people, not to everyone!

In order to have a specific column order You need to select column by column in the order You wish. Selection order dictates how columns will be ordered in output.

Try this command:

alter table students modify age int(5) first; 

This will change the position of age to the first position.

This worked for me on Oracle DB:

select column1, column2, t.* from table t