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
| DECLARE @ncvRequete AS NVARCHAR(4000);
DECLARE @contraintName as VARCHAR(4000)
DECLARE @nameTable as VARCHAR(4000)
DECLARE @nameTable2 as VARCHAR(4000)
DECLARE @schemaTable as VARCHAR(4000)
DECLARE cur CURSOR FOR
SELECT
pk.TABLE_SCHEMA AS PK_TableSchema,
pk.TABLE_NAME AS PK_TableName,
fk.CONSTRAINT_NAME AS FK_ConstraintName
--fk.TABLE_SCHEMA AS FK_TableSchema,
,fk.TABLE_NAME AS FK_TableName
--fk_col.COLUMN_NAME AS FK_ColumnName,
--,pk_col.ORDINAL_POSITION AS OrdinalPosition
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk_col ON
pk_col.CONSTRAINT_SCHEMA = pk.CONSTRAINT_SCHEMA AND
pk_col.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON
rc.UNIQUE_CONSTRAINT_SCHEMA = pk.CONSTRAINT_SCHEMA AND
rc.UNIQUE_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON
fk.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA AND
fk.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fk_col ON
fk_col.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA AND
fk_col.CONSTRAINT_NAME = rc.CONSTRAINT_NAME AND
fk_col.ORDINAL_POSITION = pk_col.ORDINAL_POSITION
WHERE
pk.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
fk.CONSTRAINT_TYPE = 'FOREIGN KEY'
--AND PK_TableSchema = 'dbo'
ORDER BY
pk.TABLE_SCHEMA,
pk.TABLE_NAME,
fk.CONSTRAINT_NAME,
fk.TABLE_SCHEMA,
fk.TABLE_NAME,
pk_col.ORDINAL_POSITION
OPEN cur;
FETCH NEXT FROM cur INTO @schemaTable, @nameTable, @contraintName, @nameTable2
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'['+@schemaTable+'].['+@nameTable+']') AND name = N''+@contraintName+'')
--IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'['+@schemaTable+'].[' + @contraintName +']') AND parent_object_id = OBJECT_ID(N'['+@schemaTable+'].['+@nameTable+']'))
BEGIN
SET @ncvRequete = 'ALTER TABLE ['+ @schemaTable+'].['+@nameTable+'] DROP CONSTRAINT [' + @contraintName +']'
PRINT @ncvRequete + ' ... T1' +@nameTable;
END
ELSE
BEGIN
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'['+@schemaTable+'].[' + @contraintName +']') AND parent_object_id = OBJECT_ID(N'['+@schemaTable+'].['+@nameTable2+']'))
BEGIN
SET @ncvRequete = 'ALTER TABLE ['+ @schemaTable+'].['+@nameTable2+'] DROP CONSTRAINT [' + @contraintName +']'
PRINT @ncvRequete + ' ... T2' +@nameTable;
END
END
EXECUTE sp_executeSql @ncvRequete;
FETCH NEXT FROM cur INTO @schemaTable, @nameTable, @contraintName, @nameTable2
END;
CLOSE cur;
DEALLOCATE cur; |
Partager