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
