/*-------------------------------------------------------------------
[SCRIPT] Generate FK scripts.sql
[DESCRIPTION] Génère les scripts de création et suppression de FK
[AUTHOR] Origine : Seenivasan
[MAJ PAR] Modifié par A. Papillon - Datafly
-------------------------------------------------------------------*/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET NOCOUNT ON;
DECLARE @FKName NVARCHAR(128);
DECLARE @FKColumnName NVARCHAR(128);
DECLARE @PKColumnName NVARCHAR(128);
DECLARE @fTableName NVARCHAR(128);
DECLARE @fSchemaName NVARCHAR(128);
DECLARE @fUpdateRule INT;
DECLARE @fDeleteRule INT;
DECLARE @FieldNames NVARCHAR(500);
IF OBJECT_ID('tempdb..#Temp') <> 0
DROP TABLE #Temp;
CREATE TABLE #Temp
(
PKTABLE_QUALIFIER NVARCHAR(128) COLLATE DATABASE_DEFAULT
, PKTABLE_OWNER NVARCHAR(128) COLLATE DATABASE_DEFAULT
, PKTABLE_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
, PKCOLUMN_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
, FKTABLE_QUALIFIER NVARCHAR(128) COLLATE DATABASE_DEFAULT
, FKTABLE_OWNER NVARCHAR(128) COLLATE DATABASE_DEFAULT
, FKTABLE_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
, FKCOLUMN_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
, KEY_SEQ INT
, UPDATE_RULE INT
, DELETE_RULE INT
, FK_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
, PK_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
, DEFERRABILITY INT
);
DECLARE TTableNames CURSOR FOR
SELECT name
, SCHEMA_NAME(uid) AS schemaname
FROM sysobjects
WHERE xtype = 'U'
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND OBJECT_NAME(id) <> 'sysdiagrams'; -- seulement tables utilisateur
OPEN TTableNames;
FETCH NEXT FROM TTableNames
INTO @fTableName
, @fSchemaName;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #Temp
EXEC dbo.sp_fkeys @fTableName
, @fSchemaName;
FETCH NEXT FROM TTableNames
INTO @fTableName
, @fSchemaName;
END;
CLOSE TTableNames;
DEALLOCATE TTableNames;
IF OBJECT_ID('tempdb..#Temp1') <> 0
DROP TABLE #Temp1;
SET @FieldNames = '';
SET @fTableName = '';
SELECT DISTINCT FK_NAME AS FKName
, FKTABLE_NAME AS FTName
, FKTABLE_OWNER AS FSchema
, @FieldNames AS FTFields
, PKTABLE_NAME AS STName
, PKTABLE_OWNER AS STSchema
, @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 * FROM #Temp1 JOIN sys.foreign_keys ON #Temp1.FKName = sys.foreign_keys.name
SELECT 'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''['
+ FSchema + '].[' + FKName
+ ']'') AND parent_object_id = OBJECT_ID(N''[' + FSchema + '].['
+ FTName + ']'')) ALTER TABLE [' + FSchema + '].[' + FTName
+ '] WITH NOCHECK ADD CONSTRAINT [' + FKName + '] FOREIGN KEY ('
+ FTFields + ') REFERENCES [' + STSchema + '].[' + STName + '] ('
+ STFields + ') ' + FKType
+ CASE WHEN sys.foreign_keys.is_not_for_replication = 1 THEN
' NOT FOR REPLICATION'
ELSE ''
END AS '--CREATE FK Constraints'
, 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''['
+ FSchema + '].[' + FKName + ']'')) ALTER TABLE [' + FSchema + '].['
+ FTName + '] DROP CONSTRAINT [' + FKName + '];' AS '--DROP FK Constraints'
FROM #Temp1
JOIN sys.foreign_keys ON #Temp1.FKName = sys.foreign_keys.name;
SET NOCOUNT OFF;
