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

user_73794 0
Viewed 116825

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

change-management database-table sql-server
10 Answers

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.

user_836670

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.

enter image description here

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

enter image description here

user_652230

To my knowledge there is no known method to change the order of the column. Behind the scenes SQL Management Studio does what Jose Basilio said. And if you have a big table then it is impractical to change the column orders like this way.

You can use a "view". With SQL views you can use any order you like without getting affected by the table column changes.

user_734700

I am using SSMS 18. I did in simple way

  • Opened design of table
  • positioning the required column by dragging it
  • And as per the answer from KM (second in thread) - uncheck the option to allow creation of change scripts refer image above.
  • Save the changes.
  • Done. Check your table now.
user_84030130

Select all the columns into a temp table, and create a new table with the new column you want. Then drop the old table, select all the columns from the temp table, and insert them into the new table with the reordered column. No data is lost.

SELECT * FROM TEMP
SELECT * FROM originaltbl
SELECT * FROM #Stagintbl

DECLARE @ColumnName nvarchar(max);
SET @ColumnName=(SELECT
    DISTINCT STUFF((
        SELECT ',' + a.COLUMN_NAME
        FROM (
            SELECT Column_name 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME='originaltbl') a
        for xml path('')
    ),1,1,'') AS ColumnName)

DECLARE @Sqlquery nvarchar(max)
SET @Sqlquery = 'SELECT ' + @ColumnName + ' FROM #Stagintbl' + '';
INSERT INTO originaltbl
EXECUTE(@Sqlquery)
user_108202890

I am not sure if the thread is still active. I was having the same query with MySQL database. Right clicking the table and selecting 'ALTER' auto generated the below code. Sample provided from sakila db and it worked. Just find out the column after which you want to place your new column and use 'AFTER' keyword

ALTER TABLE `sakila`.`actor` 
CHANGE COLUMN `middle_name` `middle_name` VARCHAR(50) NULL DEFAULT NULL AFTER `first_name`;
user_98708350
Related