How to Change the Order of Columns in a Table( SQL Server)
Alter table - Add new column in between..
Change the Order of Columns in a Table:
This scirpt an easy way to do this. It's very wrong though. It's very dangerous though and can really mess some things up. When you run the script in Query Analyzer, run it in text results mode. Make sure you run it in pieces.
Alter table - Add new column in between..
Change the Order of Columns in a Table:
This scirpt an easy way to do this. It's very wrong though. It's very dangerous though and can really mess some things up. When you run the script in Query Analyzer, run it in text results mode. Make sure you run it in pieces.
USE Northwind
GO
PRINT 'This script attempts to reorder a column by updating the system tables themselves. '
CREATE TABLE crazy_test(
identcol INT IDENTITY(1,1),
col1 INT,
col2 INT,
col3 INT,
CONSTRAINT pk_crazy_test PRIMARY KEY NONCLUSTERED (identcol ASC))
CREATE CLUSTERED INDEX idx_crazy_test ON crazy_test(col2 DESC)
INSERT crazy_test(col1, col2, col3)
SELECT 1,8,9 UNION ALL
SELECT 2,7,11 UNION ALL
SELECT 3,6,10 UNION ALL
SELECT 4,5,12
PRINT 'Order works as expected'
SELECT * FROM crazy_test
PRINT 'Notice the colorder, which you would think "orders" the table columns.'
SELECT so.name AS table_name, sc.name AS column_name, sc.colid, sc.colorder
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
--ONLY RUN THE SCRIPT TO HERE. TAKE TIME TO LOOK AND SEE WHAT YOU HAVE.
--Look in enterprise manager to see rows are ordered as expected. Make sure you right-click on the Tables and refresh first.
--Now set the server to allow updates directly on the system tables and update the column order.
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE sc
SET sc.colorder =
CASE
WHEN sc.colorder = 3 THEN 2
WHEN sc.colorder = 2 THEN 3
ELSE sc.colorder
END
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
GO
PRINT 'Notice the new colorder.'
SELECT so.name AS table_name, sc.name AS column_name, sc.colid, sc.colorder
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
GO
DBCC FREEPROCCACHE
PRINT 'Order works the same in Query Analyzer though.'
SELECT * FROM crazy_test
PRINT 'The indexes are still also on the right columns.'
EXEC sp_helpindex 'crazy_test'
--ONLY RUN THE SCRIPT TO HERE. TAKE TIME TO LOOK AND SEE WHAT YOU HAVE.
--If you check the Enterprise Manager table views and the diagrams, the order also has not changed.
----Again, make sure you hit the refresh first in Enterprise Manager before checking.
--The indexes also have not been changed.
--*****Now, we will change the colid like we did the column order. We'll rerun the test from scratch so it's a fair test.
--DO NOT RUN THIS ON A PRODUCTION SYSTEM. THIS IS JUST FOR TESTING.
GO
DROP TABLE crazy_test
GO
EXEC sp_configure 'allow updates',0
GO
RECONFIGURE
GO
CREATE TABLE crazy_test(
identcol INT IDENTITY(1,1),
col1 INT,
col2 INT,
col3 INT,
CONSTRAINT pk_crazy_test PRIMARY KEY NONCLUSTERED (identcol ASC))
CREATE CLUSTERED INDEX idx_crazy_test ON crazy_test(col2 DESC)
INSERT crazy_test(col1, col2, col3)
SELECT 1,8,9 UNION ALL
SELECT 2,7,11 UNION ALL
SELECT 3,6,10 UNION ALL
SELECT 4,5,12
PRINT 'Order works as expected'
SELECT * FROM crazy_test
PRINT 'Notice the colid.'
SELECT so.name AS table_name, sc.name AS column_name, sc.colid, sc.colorder
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
--ONLY RUN THE SCRIPT TO HERE. TAKE TIME TO LOOK AND SEE WHAT YOU HAVE.
--Look in enterprise manager to see rows are ordered as expected. Make sure you right-click on the Tables and refresh first.
--Now set the server to allow updates directly on the system tables and update the column id.
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE sc
SET sc.colid =
CASE
WHEN sc.colid = 3 THEN 2
WHEN sc.colid = 2 THEN 3
ELSE sc.colid
END
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
GO
PRINT 'Notice the new colid.'
SELECT so.name AS table_name, sc.name AS column_name, sc.colid, sc.colorder
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
GO
DBCC FREEPROCCACHE
PRINT 'Order is now different in Query Analyzer.'
SELECT * FROM crazy_test
PRINT 'We messed up the indexes though. Notice, it now shows col1 as the indexed column.'
PRINT 'THIS IS WHY THIS SHOULD NEVER BE RAN ON A PRODUCTION SYSTEM. JUST RECREATE THE TABLE IF YOU'
PRINT 'NEED A NEW ORDER. ORDER SHOULD NOT BE IMPORTANT IN THE RDMS THOUGH, SO YOU PROBABLY HAVE BAD DESIGN'
EXEC sp_helpindex 'crazy_test'
--ONLY RUN THE SCRIPT TO HERE. TAKE TIME TO LOOK AND SEE WHAT YOU HAVE.
--If you check the Enterprise Manager table views and the diagrams, the order has been changed both in table design and the diagram tool.
--Erase the test tables and reset the system to not allow system table updates.
GO
DROP TABLE crazy_test
GO
EXEC sp_configure 'allow updates',0
GO
RECONFIGURE
GO
|