Vous êtes ici :
Cette requête génère les scripts de création et de suppression de contraintes FK (clés étrangères) dans la base de données courante.
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 |
/*------------------------------------------------------------------- [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; |
Table of Contents