CODEWITHSUNDEEP
sql-server
Nashirmohammad Marediya
Nashirmohammad Marediya

Reputation: 13

Changing the Order of Columns in a Table Permanently Using T-SQL query

My table is student_register and has four columns like

1. srno 
2. firstname
3. lastname
4. middlename

I want the column middlename in third position permanently in table using SQL Server 2005 query.

Thank you.

Upvotes: 0

Views: 5777

Answers (2)

Adam Plocher
Adam Plocher

Reputation: 14233

There's no way to do what you want without dropping and readding the table. To see how Management Studio accomplishes this (at least in 2012, but I think this applies to 2005 too), go to Management Studio's Options > Designers > Table and Database Designer > Uncheck Prevent saving changes that require table re-creation.

Once you do that you can go in to the table designer, change the order of the columns and instead of clicking "Save", click "Save Change Script" to see the SQL.

Here is what SQL 2012 would generate:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Table_1
    (
    TEst1 nchar(10) NULL,
    Test3 nchar(10) NULL,
    Test2 nchar(10) NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Table_1 SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.Table_1)
     EXEC('INSERT INTO dbo.Tmp_Table_1 (TEst1, Test3, Test2)
        SELECT TEst1, Test3, Test2 FROM dbo.Table_1 WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Table_1
GO
EXECUTE sp_rename N'dbo.Tmp_Table_1', N'Table_1', 'OBJECT' 
GO
COMMIT

Upvotes: 4

peterm
peterm

Reputation: 92785

Since it's a one time thing the simplest approach would be to use Visual Database Tools for that

How to: Change Column Order (Visual Database Tools) SQL Server 2005

  • In Object Explorer, right-click the table with columns you want to reorder and click Design (Modify in SP1 or earlier). The table opens in Table Designer.
  • Select the box to the left of the column name that you want to reorder.
  • Drag the column to another location within the table.

To do it in TSQL you'll need:

  • create a temp table with correct order of columns
  • insert data into temp table from original table
  • drop original table
  • rename temp table to original name

Upvotes: 1

Related Questions