sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | Disable all FK

DECLARE @CMD NVARCHAR(MAX)
DECLARE @CONTAINER TABLE (CMD NVARCHAR(MAX)) 

;WITH A AS (
SELECT 
 UPPER(T.OBJECT_ID) OBJECT_ID
 ,UPPER(T.NAME) [TABLE NAME]
 ,UPPER(SCH.NAME) [SCHEMA NAME]
 ,UPPER(C.COLUMN_ID) COLUMN_ID
 ,UPPER(C.NAME) [COLUMN NAME]
 ,UPPER(TYP.NAME + ISNULL(CASE 
 WHEN TYP.NAME IN ('NVARCHAR', 'NTEXT', 'NCHAR') 
 THEN '(' + CASE C.MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CAST(C.MAX_LENGTH / 2 AS NVARCHAR) END + ')'
 WHEN TYP.NAME IN ('VARCHAR', 'TEXT', 'CHAR', 'BINARY', 'VARBINARY')
 THEN '(' + CASE C.MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CAST(C.MAX_LENGTH AS NVARCHAR) END + ')'
 WHEN TYP.NAME IN ('DATETIME2', 'DATETIMEOFFSET', 'TIME')
 THEN '(' + CAST(C.SCALE AS NVARCHAR) + ')'
 WHEN TYP.NAME IN ('DECIMAL', 'NUMERIC')
 THEN '(' + CAST(C.PRECISION AS NVARCHAR) + ', ' + CAST(C.SCALE AS NVARCHAR) + ')'
 END, '')) TYPE
 ,UPPER(CASE C.IS_NULLABLE WHEN 1 THEN 'NULLABLE' ELSE 'NOT NULL' END) NULLABLE
 
 ,UPPER(ISNULL(KC.TYPE, '')) PK
 ,UPPER(ISNULL(KC2.NAME, '')) UK
 ,UPPER(CASE 
 WHEN INDCOL.IS_DESCENDING_KEY IS NULL THEN '' 
 WHEN INDCOL.IS_DESCENDING_KEY = 1 THEN '1' 
 ELSE '0' END)  [DESC]
 ,UPPER(ISNULL(FK.name, '')) FK_NAME
 ,UPPER(CASE ISNULL(FKC.CONSTRAINT_COLUMN_ID, '') WHEN 0 THEN '' ELSE 'FK' END) FK
 ,UPPER(ISNULL(SCHEMA_NAME(REF.SCHEMA_ID), '')) FK_REF_TBLSCH
 ,UPPER(ISNULL(REF.NAME, '')) FK_REF_TBL
 ,UPPER(ISNULL(REFCOL.NAME, '')) FK_REF_TBLCOL
 ,UPPER(CASE C.IS_IDENTITY 
 WHEN 1 THEN 'IDENTITY(' + 
 CAST(IC.SEED_VALUE AS NVARCHAR) + ', ' + 
 CAST(IC.INCREMENT_VALUE AS NVARCHAR) + ')'  
 ELSE '' END) [IDENTITY]
 ,UPPER(C.IS_NULLABLE) IS_NULLABLE
 
 ,UPPER(C.MAX_LENGTH) MAX_LENGTH
 ,UPPER(C.PRECISION) [PRECISION]
 ,UPPER(C.SCALE) SCALE
 ,UPPER(C.USER_TYPE_ID) USER_TYPE_ID
 ,UPPER(C.SYSTEM_TYPE_ID) SYSTEM_TYPE_ID
 ,UPPER(C.IS_COMPUTED) IS_COMPUTED
 ,UPPER(C.default_object_id) default_object_id
 ,UPPER(ISNULL(object_definition(C.default_object_id), '')) [DEFAULT]

FROM 
 SYS.ALL_COLUMNS C 
 INNER JOIN SYS.TABLES T
 ON C.OBJECT_ID = T.OBJECT_ID
 INNER JOIN SYS.SCHEMAS SCH
 ON T.SCHEMA_ID = SCH.SCHEMA_ID
 INNER JOIN SYS.OBJECTS O
 ON T.OBJECT_ID = O.OBJECT_ID
 AND O.IS_MS_SHIPPED = 0
 INNER JOIN SYS.TYPES TYP
 ON C.USER_TYPE_ID = TYP.USER_TYPE_ID
 LEFT JOIN SYS.IDENTITY_COLUMNS IC
 ON IC.OBJECT_ID = C.OBJECT_ID 
 AND IC.COLUMN_ID = C.COLUMN_ID
 LEFT JOIN SYS.INDEX_COLUMNS INDCOL
 ON INDCOL.OBJECT_ID = T.OBJECT_ID
 AND INDCOL.COLUMN_ID = C.COLUMN_ID
 --AND INDCOL.INDEX_ID = 1

 LEFT JOIN SYS.FOREIGN_KEY_COLUMNS FKC
 ON FKC.PARENT_COLUMN_ID = C.COLUMN_ID
 AND FKC.PARENT_OBJECT_ID = T.OBJECT_ID
 
 LEFT JOIN SYS.foreign_keys FK
 ON FK.referenced_object_id = FKC.referenced_object_id
 AND FK.parent_object_id = T.OBJECT_ID
 AND FKC.constraint_object_id = FK.object_id

 LEFT JOIN SYS.TABLES REF 
 ON REF.OBJECT_ID = FKC.REFERENCED_OBJECT_ID
 
 LEFT JOIN SYS.COLUMNS REFCOL 
 ON REFCOL.COLUMN_ID = FKC.REFERENCED_COLUMN_ID
 AND REFCOL.OBJECT_ID = REF.OBJECT_ID

 
 LEFT JOIN SYS.INDEX_COLUMNS INDCOL2
 ON INDCOL2.OBJECT_ID = T.OBJECT_ID
 AND C.COLUMN_ID = INDCOL2.COLUMN_ID
 LEFT JOIN SYS.KEY_CONSTRAINTS KC 
 ON T.OBJECT_ID = KC.PARENT_OBJECT_ID
 AND INDCOL2.INDEX_ID = KC.UNIQUE_INDEX_ID
 AND KC.TYPE = 'PK'
 LEFT JOIN SYS.INDEX_COLUMNS INDCOL3
 ON INDCOL3.OBJECT_ID = T.OBJECT_ID
 AND C.COLUMN_ID = INDCOL3.COLUMN_ID
 LEFT JOIN SYS.KEY_CONSTRAINTS KC2
 ON T.OBJECT_ID = KC2.PARENT_OBJECT_ID
 AND INDCOL3.INDEX_ID = KC2.UNIQUE_INDEX_ID
 AND KC2.TYPE = 'UQ'
 --LEFT JOIN SYS.OBJECTS ODF 
 --ON ODF.OBJECT_ID = C.default_object_id
--WHERE T.NAME ='SYSTEMCOREACCOUNT'
--ORDER BY [TABLE NAME], COLUMN_ID
, B AS (SELECT DISTINCT '[' + [SCHEMA NAME] + '].[' +  [TABLE NAME] + ']' [TABLE NAME], FK_NAME FROM A WHERE FK <> '')

INSERT INTO @CONTAINER
SELECT 'ALTER TABLE ' + [TABLE NAME] + ' NOCHECK CONSTRAINT [' + FK_NAME + ']' FROM B

DECLARE CUR CURSOR FOR
SELECT * FROM @CONTAINER

OPEN CUR

FETCH NEXT FROM CUR INTO @CMD
WHILE(@@FETCH_STATUS = 0)
BEGIN
PRINT @CMD
--EXECUTE (@CMD)

FETCH NEXT FROM CUR INTO @CMD
END
CLOSE CUR
DEALLOCATE CUR
Comments