Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
167 lines (145 sloc) 6.15 KB
DECLARE @PKTableName VARCHAR(100)
,@PKName VARCHAR(100)
,@ClusteredIndexName VARCHAR(100)
,@ClusteredIndexColumns VARCHAR(100)
,@FKName VARCHAR(100)
,@sql VARCHAR(MAX)
,@PKcolumnName VARCHAR(30)
,@FKtable VARCHAR(100)
,@FKColumnName VARCHAR(100)
,@parentColumnNumber INT
SET @PKTableName = 'PKTest_Main'
IF OBJECT_ID('tempdb..#FKAgainstTableList') IS NOT NULL DROP TABLE #FKAgainstTableList
CREATE TABLE #FKAgainstTableList
(
FKTable VARCHAR(100)
,FKName VARCHAR(100)
,FKColumnName VARCHAR(100)
)
IF OBJECT_ID('tempdb..#PKDetails') IS NOT NULL DROP TABLE #PKDetails
CREATE TABLE #PKDetails
(
PKName VARCHAR(100)
,PKColumnName VARCHAR(100)
,Ordinal_Position INT
)
IF OBJECT_ID('tempdb..#ClusteredIndexDetails') IS NOT NULL DROP TABLE #ClusteredIndexDetails
CREATE TABLE #ClusteredIndexDetails
(
Clustered_Index_Name VARCHAR(100)
,Clustered_Index_Column_Name VARCHAR(100)
,Ordinal_Position INT
)
INSERT INTO #PKDetails (PKName, PKColumnName, Ordinal_Position)
SELECT
CONSTRAINT_NAME
,COLUMN_NAME
,ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
AND table_name = @PKTableName
INSERT INTO #ClusteredIndexDetails (Clustered_Index_Name, Clustered_Index_Column_Name, Ordinal_Position)
SELECT
i.name
,ac.name
,ic.key_ordinal
FROM sys.objects o
INNER JOIN sys.indexes i
ON o.object_id = i.object_id
INNER JOIN sys.index_columns ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN sys.all_columns ac
ON i.object_id = ac.object_id
AND ic.column_id = ac.column_id
WHERE o.name = @PKTableName and i.type = 1
SET @PKName = (SELECT DISTINCT PKName FROM #PKDetails)
SELECT @PKcolumnName = COALESCE(@PKcolumnName + ' ASC,', '') + PKColumnName FROM #PKDetails ORDER BY ORDINAL_POSITION ASC
SELECT @ClusteredIndexColumns = COALESCE(@ClusteredIndexColumns + ' ASC,', '') + Clustered_Index_Column_Name FROM #ClusteredIndexDetails ORDER BY ORDINAL_POSITION ASC
PRINT @PKcolumnName
PRINT @ClusteredIndexColumns
/* Let's grab the foreign keys and put them into a temp table */
INSERT INTO #FKAgainstTableList (FKTable, FKName, FKColumnName)
SELECT DISTINCT
KC.TABLE_NAME
,KC.CONSTRAINT_NAME
,STUFF((SELECT ',' + KCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
INNER JOIN sys.foreign_keys FK ON KCU.CONSTRAINT_NAME = FK.name
WHERE OBJECT_NAME(fk.referenced_object_id) = @PKTableName AND KCU.CONSTRAINT_NAME = KC.CONSTRAINT_NAME
ORDER BY ORDINAL_POSITION ASC
FOR XML PATH('')
),1,1,'')
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KC
WHERE STUFF((SELECT ',' + KCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
INNER JOIN sys.foreign_keys FK ON KCU.CONSTRAINT_NAME = FK.name
WHERE OBJECT_NAME(fk.referenced_object_id) = @PKTableName AND KCU.CONSTRAINT_NAME = KC.CONSTRAINT_NAME
ORDER BY ORDINAL_POSITION ASC
FOR XML PATH('')
),1,1,'') IS NOT NULL
DECLARE cursor1 CURSOR FOR
SELECT * FROM #FKAgainstTableList
PRINT @sql
/* Disable constraint on FK Tables */
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @FKtable,@FKName,@FKColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql ='ALTER TABLE '+@FKtable+' DROP CONSTRAINT '+ @FKName
PRINT @sql
--EXEC(@sql)
FETCH NEXT FROM cursor1 INTO @FKtable,@FKName,@FKColumnName
END
CLOSE cursor1
DEALLOCATE cursor1
/* Let's drop that PK */
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(@PKTableName) AND name = @PKName)
BEGIN
SET @sql = 'ALTER TABLE '+@PKTableName+' DROP CONSTRAINT '+ @PKName
PRINT @sql
--EXEC(@sql)
END
/* But what if the clustered index is not the same as the PK? Let's drop that too */
IF EXISTS (SELECT 1 FROM sys.objects o JOIN sys.indexes i ON o.object_id = i.object_id WHERE o.name = @PKTableName and i.type = 1)
BEGIN
SET @ClusteredIndexName = (SELECT i.name FROM sys.objects o JOIN sys.indexes i ON o.object_id = i.object_id WHERE o.name = @PKTableName and i.type = 1)
SET @sql = 'DROP INDEX ' + @ClusteredIndexName + ' ON ' +@PKTableName
PRINT @sql
--EXEC(@sql)
END
/* OK, let's apply that PK but cluster it this time */
SET @sql = 'ALTER TABLE '+@PKTableName +' ADD CONSTRAINT '+@PKName+' PRIMARY KEY CLUSTERED ('+@PKcolumnName+' ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]'
PRINT(@sql)
--EXEC(@sql)
/* Put the FK's back on */
DECLARE cursor2 CURSOR FOR
SELECT * FROM #FKAgainstTableList
OPEN cursor2
FETCH NEXT FROM cursor2 INTO @FKtable,@FKName,@FKColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER TABLE '+@FKtable+' WITH CHECK ADD CONSTRAINT '+ @FKName+' FOREIGN KEY(['+REPLACE(@FKColumnName,',','],[')+'])
REFERENCES ['+@PKTableName+'] (['+REPLACE(@PKcolumnName,' ASC,','],[')+'])'
PRINT(@sql)
--EXEC(@sql)
FETCH NEXT FROM cursor2 INTO @FKtable,@FKName,@FKColumnName
END
CLOSE cursor2
DEALLOCATE cursor2
/* Check if the old Clustered index is different from the Primary Key, if it is, let's apply old clustered index as a nonclustered index */
IF NOT EXISTS (SELECT * FROM #ClusteredIndexDetails CID JOIN #PKDetails PKD ON CID.Clustered_Index_Column_Name = PKD.PKColumnName AND CID.Ordinal_Position = PKD.Ordinal_Position)
BEGIN
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE Name = 'IX_' + @PKTableName + '_' + REPLACE(@ClusteredIndexColumns,' ASC,','_'))
BEGIN
SET @sql = N'CREATE NONCLUSTERED INDEX [IX_' + @PKTableName + '_' + REPLACE(@ClusteredIndexColumns,' ASC,','_') + '] ON [' + @PKTableName + '] ([' + REPLACE(@ClusteredIndexColumns,' ASC,','] ASC,[') + '] ASC)' +
' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]'
PRINT(@sql)
--EXEC(@sql)
END
END
/* Tidy up */
DROP TABLE #FKAgainstTableList
DROP TABLE #PKDetails
DROP TABLE #ClusteredIndexDetails