Scripter les contraintes de clé étrangère

Lorsqu'on effectue des chargements de données (avec SSIS par exemple), il est fréquent de supprimer temporairement les contraintes de clé étrangère : c'est nécessaire par exemple pour vider les tables avec une commande TRUNCATE TABLE... Lors des étapes de conception, j'ai donc besoin d'obtenir le script de suppression et de création de mes contraintes.

Je vous propose les 2 scripts suivants :

1. Générer le script de suppression des contraintes existantes

  if object_id('tempdb..#ConstraintConfig') is not NULL
DROP TABLE #ConstraintConfig
CREATE TABLE [#ConstraintConfig]
(
[DATABASE] [nvarchar](128) NULL
, [TABLE_SCHEMA] [nvarchar](128) NULL
, [TABLE_NAME] [nvarchar](128) NULL
, [CONSTRAINT_SCHEMA] [nvarchar](128) NULL
, [CONSTRAINT_NAME] [sysname] NOT NULL
, [NotForReplication] [int] NULL
)
insert  #ConstraintConfig
select  CONSTRAINT_CATALOG as 'DATABASE'
, TABLE_SCHEMA
, TABLE_NAME
, CONSTRAINT_SCHEMA
, CONSTRAINT_NAME
, OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' +
CONSTRAINT_NAME), 'CnstIsNotRepl') as NotForReplication
from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'
SELECT  'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' +
CONSTRAINT_NAME + ''')) ALTER TABLE [' + TABLE_SCHEMA + '].[' +
TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']'
FROM    #ConstraintConfig

Générer le script de création des contraintes existantes

(à partir d'un exemple de Seenivasan)

  SET NOCOUNT ON
DECLARE @FKName NVARCHAR(128)
DECLARE @FKColumnName NVARCHAR(128)
DECLARE @PKColumnName NVARCHAR(128)
DECLARE @fTableName NVARCHAR(128)
DECLARE @fUpdateRule INT
DECLARE @fDeleteRule INT
DECLARE @FieldNames NVARCHAR(500)
CREATE TABLE #Temp
(
PKTABLE_QUALIFIER NVARCHAR(128)
, PKTABLE_OWNER NVARCHAR(128)
, PKTABLE_NAME NVARCHAR(128)
, PKCOLUMN_NAME NVARCHAR(128)
, FKTABLE_QUALIFIER NVARCHAR(128)
, FKTABLE_OWNER NVARCHAR(128)
, FKTABLE_NAME NVARCHAR(128)
, FKCOLUMN_NAME NVARCHAR(128)
, KEY_SEQ INT
, UPDATE_RULE INT
, DELETE_RULE INT
, FK_NAME NVARCHAR(128)
, PK_NAME NVARCHAR(128)
, DEFERRABILITY INT
)
DECLARE TTableNames CURSOR
FOR SELECT name
FROM sysobjects
WHERE xtype = 'U'
OPEN TTableNames
FETCH NEXT FROM TTableNames INTO @fTableName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #Temp
EXEC dbo.sp_fkeys @fTableName
FETCH NEXT FROM TTableNames INTO @fTableName
END
CLOSE TTableNames
DEALLOCATE TTableNames
SET @FieldNames = ''
SET @fTableName = ''
SELECT DISTINCT
FK_NAME AS FKName
, FKTABLE_NAME AS FTName
, @FieldNames AS FTFields
, PKTABLE_NAME AS STName
, @FieldNames AS STFields
, @FieldNames AS FKType
INTO #Temp1
FROM #Temp
ORDER BY FK_NAME
, FKTABLE_NAME
, PKTABLE_NAME
DECLARE FK_CUSROR CURSOR
FOR SELECT FKName
FROM #Temp1
OPEN FK_CUSROR
FETCH FROM FK_CUSROR INTO @FKName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE FK_FIELDS_CUSROR CURSOR
FOR SELECT FKCOLUMN_NAME
, PKCOLUMN_NAME
, UPDATE_RULE
, DELETE_RULE
FROM #TEMP
WHERE FK_NAME = @FKName
ORDER BY KEY_SEQ
OPEN FK_FIELDS_CUSROR
FETCH FROM FK_FIELDS_CUSROR INTO @FKColumnName, @PKColumnName,
@fUpdateRule, @fDeleteRule
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #Temp1
SET FTFields = CASE WHEN LEN(FTFields) = 0
THEN '[' + @FKColumnName + ']'
ELSE FTFields + ',[' + @FKColumnName + ']'
END
WHERE FKName = @FKName
UPDATE #Temp1
SET STFields = CASE WHEN LEN(STFields) = 0
THEN '[' + @PKColumnName + ']'
ELSE STFields + ',[' + @PKColumnName + ']'
END
WHERE FKName = @FKName
FETCH NEXT FROM FK_FIELDS_CUSROR INTO @FKColumnName, @PKColumnName,
@fUpdateRule, @fDeleteRule
END
UPDATE #Temp1
SET FKType = CASE WHEN @fUpdateRule = 0
THEN FKType + ' ON UPDATE CASCADE'
ELSE FKType
END
WHERE FKName = @FKName
UPDATE #Temp1
SET FKType = CASE WHEN @fDeleteRule = 0
THEN FKType + ' ON DELETE CASCADE'
ELSE FKType
END
WHERE FKName = @FKName
CLOSE FK_FIELDS_CUSROR
DEALLOCATE FK_FIELDS_CUSROR
FETCH next FROM FK_CUSROR INTO @FKName
END
CLOSE FK_CUSROR
DEALLOCATE FK_CUSROR
SELECT 'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[' +
FKName + ']'') AND parent_object_id = OBJECT_ID(N''[dbo].[' + FTName +
']'')) ALTER TABLE [dbo].[' + FTName + '] ADD CONSTRAINT [' + FKName +
'] FOREIGN KEY (' + FTFields + ') REFERENCES [' + STName + '] (' +
STFields + ') ' + FKType
FROM #Temp1
SET NOCOUNT OFF