September 23, 2011 at 4:28 am
This script should use a Table name as provided, and look for all the foreign keys originating from this table, create a script to drop them, then drop the pk, then recreate it as a clustered constraint and then recreate the foreign keys using the pk column name, table name and the column name in the FK table.
So far this only works with Default schema.
The need has arose from a table having 23 FK constraints originating from the pk, making a modification to the property of the pk a bit tough.
Other than omitting the schema, is there anything that I may have missed wrt the foreign keys?
I don't know much about fk properties.
Most importantly, is there a simpler method to getting the information than I have done here?
DECLARE @PKTableName VARCHAR(100),
@PKName varchar(100),
@FKName varchar(100),
@sql varchar(max),
@PKcolumnName varchar(30),
@table VARCHAR(30),
@FKColumnName VARCHAR(100)
SET @PKTableName = 'TableName'
set @PKName = ( SELECT name
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = @PKTableName
AND is_primary_key = 1
) --Get the name of the PK
set @PKcolumnName = ( SELECT name
FROM sys.columns
WHERE OBJECT_NAME(object_id) = @PKTableName
AND is_identity = 1
) --Get the Name of the Column of the PK
IF EXISTS ( SELECT *
FROM sys.indexes
WHERE object_ID(@PKTableName) = object_id
AND index_id = 0 ) -- IF a heap
begin
IF EXISTS ( SELECT *
FROM sys.tables
WHERE object_id = OBJECT_ID(N'[dbo].[FKAgainstTableList]') )
BEGIN
DROP TABLE FKAgainstTableList
END
SELECT OBJECT_NAME(sys.foreign_key_columns.parent_object_id) [Table],
sys.columns.name [FKColumnName],
sys.foreign_keys.name [FKName]
INTO FKAgainstTableList
FROM sys.foreign_keys
INNER JOIN sys.foreign_key_columns ON sys.foreign_keys.object_id = sys.foreign_key_columns.constraint_object_id
INNER JOIN sys.columns ON sys.columns.object_id = sys.foreign_keys.parent_object_id
AND sys.columns.column_id = sys.foreign_key_columns.parent_column_id
WHERE OBJECT_NAME(sys.foreign_keys.referenced_object_id) = @PKTableName
DECLARE table_cur1 CURSOR
FOR SELECT *
FROM FKAgainstTableList
-------------------------------Disable constraint on FK Tables
OPEN table_cur1
FETCH NEXT FROM table_cur1 INTO @table, @FKColumnName, @FKName
WHILE@@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT '
+ @FKName
PRINT @sql
FETCH NEXT FROM table_cur1 INTO @table, @FKColumnName, @FKName
END
CLOSE table_cur1
DEALLOCATE table_cur1
--------------------------------DROP AND recreate CLUSTERED 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
END
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 = 80) ON [PRIMARY]'
PRINT ( @sql )
--------------------------------Enable FK constraints on FK tables.
DECLARE table_cur2 CURSOR
FOR SELECT *
FROM FKAgainstTableList
OPEN table_cur2
FETCH NEXT FROM table_cur2 INTO @table, @FKColumnName, @FKName
WHILE@@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER TABLE ' + @table
+ ' WITH NOCHECK ADD CONSTRAINT ' + @FKName
+ ' FOREIGN KEY([' + @FKColumnName + '])
REFERENCES [' + @PKTableName + '] ([' + @PKcolumnName + '])'
PRINT ( @sql )
SET @sql = 'ALTER TABLE ' + @table + ' CHECK CONSTRAINT '
+ @FKName
PRINT ( @sql )
FETCH NEXT FROM table_cur2 INTO @table, @FKColumnName, @FKName
END
CLOSE table_cur2
DEALLOCATE table_cur2
DROP TABLE FKAgainstTableList
end
September 23, 2011 at 8:14 am
Nice Trick 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply