Générer ses scripts de Foreign Keys
Par Arian Papillon le lundi 9 mars 2015, 20:29 - Lien permanent
Comme vous l'avez peut-être lu plus
haut, je préfère dans la mesure du possible charger mon datawarehouse en étoile
avec les contraintes de clés étrangères activées.
Pour les tables qu'il faut vider en début de chargement, le problème qui se pose donc est de supprimer temporairement les contraintes pour pouvoir lancer des commandes TRUNCATE TABLE. En effet, contrairement à l'instruction DELETE, le TRUNCATE permet de vider la table sans journaliser toutes les suppressions individuelles de lignes dans le journal de transactions. C'est donc plus rapide et moins gourmand.
Comme c'est fastidieux d'écrire un script de suppression/création de contraintes à la main (ou même de le générer avec SSMS), j'utilise les deux scripts suivants pour écrire mon code.
Les deux procédures stockées suivantes, à installer dans la base concernée,
vont nous permettre de générer les scripts sans effort.
Il suffira de les exécuter ainsi :
N'oubliez pas bien sûr de vérifier que vous avez bien sous la main le script de création avant de supprimer toutes vos foreign keys...!
Il suffira de les exécuter ainsi :
EXEC SPGetForeignKeyDropInfo;
EXEC SPGetForeignKeyCreateInfo;
N'oubliez pas bien sûr de vérifier que vous avez bien sous la main le script de création avant de supprimer toutes vos foreign keys...!
/*-------------------------------------------------------------------
[SCRIPT] SPGetForeignKeyDropInfo
[DESCRIPTION] This procedure is used for generating Foreign Key drop script.
[AUTHOR] A. Papillon
-------------------------------------------------------------------*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SPGetForeignKeyDropInfo]
AS
SET NOCOUNT ON
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
) ON [PRIMARY]
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 'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] DROP CONSTRAINT ['+CONSTRAINT_NAME+']' FROM #ConstraintConfig
SELECT 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'''+TABLE_SCHEMA+'.'+CONSTRAINT_NAME+''')) ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] DROP CONSTRAINT ['+CONSTRAINT_NAME+']' FROM #ConstraintConfig
GO
/*-------------------------------------------------------------------
[SCRIPT] SPGetForeignKeyCreateInfo
[DESCRIPTION] This procedure is used for Generating Foreign Key script.
[AUTHOR] Seenivasan
[MAJ PAR] A. Papillon
-------------------------------------------------------------------*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SPGetForeignKeyCreateInfo]
AS
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)
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
, SCHEMA_NAME(uid) AS schemaname
FROM sysobjects
WHERE xtype = 'U'
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
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 '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 + '] ADD CONSTRAINT [' + FKName
+ '] FOREIGN KEY (' + FTFields + ') REFERENCES ['+ STSchema + '].['+ STName + '] ('
+ STFields + ') ' + FKType
FROM #Temp1
SET NOCOUNT OFF
RETURN