Code to Generate the ALTER INDEX / DISABLE Command Statements
ALTER DATABASE DatabaseName
SET RESTRICTED_USER
WITH ROLLBACK AFTER 5
SET RESTRICTED_USER
WITH ROLLBACK AFTER 5
The ROLLBACK AFTER n clause at the end of the ALTER DATABASE statement lets you terminate user connections and put the database into a restricted state for modifications. As for automating the disabling of foreign key constraints, I leveraged some of the code from sp_fkeys and significantly altered it to generate the DISABLE command (similarly to how we did this in step 1 for disabling nonclustered indexes), which Listing 2 shows.
Listing 2: Code to Generate the DISABLE Command
SELECT
DISABLE_STATEMENT =
N'ALTER TABLE '
+ QUOTENAME(convert(sysname, schema_name(o2.schema_id)), N']')
+ N'.'
+ QUOTENAME(convert(sysname, o2.name), N']')
+ N' NOCHECK CONSTRAINT '
+ QUOTENAME(convert(sysname, object_name(f.object_id)), N']')
, ENABLE_STATEMENT =
N'ALTER TABLE '
+ QUOTENAME(convert(sysname, schema_name(o2.schema_id)), N']')
+ N'.'
+ QUOTENAME(convert(sysname, o2.name), N']')
+ N' WITH CHECK CHECK CONSTRAINT '
+ QUOTENAME(convert(sysname, object_name(f.object_id)), N']')
, RECHECK_CONSTRAINT =
N'SELECT OBJECTPROPERTY(OBJECT_ID('
+ QUOTENAME(convert(sysname, object_name(f.object_id)), N'''')
+ N'), ''CnstIsNotTrusted'')'
FROM
sys.objects AS o1,
sys.objects AS o2,
sys.columns AS c1,
sys.columns AS c2,
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS k
ON (k.constraint_object_id = f.object_id)
INNER JOIN sys.indexes AS i
ON (f.referenced_object_id = i.object_id
AND f.key_index_id = i.index_id)
WHERE
o1.[object_id] = object_id('tablename')
AND i.name = 'Primary key Name'
AND o1.[object_id] = f.referenced_object_id
AND o2.[object_id] = f.parent_object_id
AND c1.[object_id] = f.referenced_object_id
AND c2.[object_id] = f.parent_object_id
AND c1.column_id = k.referenced_column_id
AND c2.column_id = k.parent_column_id
ORDER BY 1, 2, 3
DISABLE_STATEMENT =
N'ALTER TABLE '
+ QUOTENAME(convert(sysname, schema_name(o2.schema_id)), N']')
+ N'.'
+ QUOTENAME(convert(sysname, o2.name), N']')
+ N' NOCHECK CONSTRAINT '
+ QUOTENAME(convert(sysname, object_name(f.object_id)), N']')
, ENABLE_STATEMENT =
N'ALTER TABLE '
+ QUOTENAME(convert(sysname, schema_name(o2.schema_id)), N']')
+ N'.'
+ QUOTENAME(convert(sysname, o2.name), N']')
+ N' WITH CHECK CHECK CONSTRAINT '
+ QUOTENAME(convert(sysname, object_name(f.object_id)), N']')
, RECHECK_CONSTRAINT =
N'SELECT OBJECTPROPERTY(OBJECT_ID('
+ QUOTENAME(convert(sysname, object_name(f.object_id)), N'''')
+ N'), ''CnstIsNotTrusted'')'
FROM
sys.objects AS o1,
sys.objects AS o2,
sys.columns AS c1,
sys.columns AS c2,
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS k
ON (k.constraint_object_id = f.object_id)
INNER JOIN sys.indexes AS i
ON (f.referenced_object_id = i.object_id
AND f.key_index_id = i.index_id)
WHERE
o1.[object_id] = object_id('tablename')
AND i.name = 'Primary key Name'
AND o1.[object_id] = f.referenced_object_id
AND o2.[object_id] = f.parent_object_id
AND c1.[object_id] = f.referenced_object_id
AND c2.[object_id] = f.parent_object_id
AND c1.column_id = k.referenced_column_id
AND c2.column_id = k.parent_column_id
ORDER BY 1, 2, 3
Use the column for DISABLE_STATEMENTS to disable the foreign key constraints, and keep the remaining information handy because you’ll need it to reenable and recheck the data, as well as verify the foreign key constraints after you’ve recreated the primary key as a unique nonclustered index.