Comment faire pour supprimer toutes les contraintes de Clé Étrangère dans tous les tableaux?

Il y a beaucoup d'information sur le sujet tout autour. Vérifier cette réponse détaillée par @AaronBertrand. Il parle de la désactivation temporaire des clés étrangères mais la lecture de tout et de modifier à volonté, vous aurez une belle script pour jouer avec et de faire beaucoup de choses.

De mon côté, je peux proposer 2 scripts différents pour obtenir toutes les clés étrangères. Sur les deux cas, décommentez la --EXEC (@SQL) pour l'exécution de votre ALTER code. Ou vous pouvez attendre jusqu'à ce qu'il imprime tous les alter clauses et ensuite de copier coller les exécuter.

Première utilise la INFORMATION_SCHEMA pour obtenir les contraintes:

DECLARE @SQL VARCHAR(MAX)=''
SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(FK.TABLE_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME) + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)
--SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
 INNER JOIN (
            SELECT i1.TABLE_NAME, i2.COLUMN_NAME
              FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
             INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME

--EXEC (@SQL)

PRINT @SQL

En utilisant différentes vues du système et un CTE table.

DECLARE @SQL varchar(4000)=''
;WITH ReferencingFK AS 
(
    SELECT fk.Name AS 'FKName', OBJECT_NAME(fk.parent_object_id) 'ParentTable',
            cpa.name 'ParentColumnName', OBJECT_NAME(fk.referenced_object_id) 'ReferencedTable',
            cref.name 'ReferencedColumnName'
    FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
    INNER JOIN sys.columns cpa ON fkc.parent_object_id = cpa.object_id AND fkc.parent_column_id = cpa.column_id
    INNER JOIN sys.columns cref ON fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id
)
SELECT @SQL = @SQL + 'ALTER TABLE ' + ParentTable + ' DROP CONSTRAINT [' + RTRIM(FKName) +'];' + CHAR(13)
--SELECT FKName, ParentTable, ParentColumnName, ReferencedTable, ReferencedColumnName
  FROM ReferencingFK
 WHERE ReferencedTable = 'Employee'
 ORDER BY ParentTable, ReferencedTable, FKName

--EXEC (@SQL) 

PRINT @SQL

OriginalL'auteur Yaroslav

J'ai amélioré le premier script fourni par @Yaroslav et le script fourni par @Dilemme de sorte qu'ils fonctionne maintenant pour les bases de données dont le SQL de la requête pour supprimer toutes les clés étrangères l'une par on dépasse la taille allouée pour la SQL variable (4000 ou MAX caractères).

La modification des scripts de déposer 5 clés étrangères par itération (juste pour être sûr, mis en œuvre par l'ajout de TOP 5). Les scripts s'arrêter quand il n'y a pas de clé étrangère de gauche à goutte (la SQL variable reste vide après l'exécution de la SELECT).

Premier script par @Yaroslav

DECLARE @SQL varchar(4000)
IterationStart:
SET @SQL=''
SELECT TOP 5 @SQL = @SQL + 'ALTER TABLE ' + FK.TABLE_NAME + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)
  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
 INNER JOIN (
            SELECT i1.TABLE_NAME, i2.COLUMN_NAME
              FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
             INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME
IF @SQL <> ''
BEGIN
  EXEC(@SQL)
  GOTO IterationStart
END

Script par @Dilemme

DECLARE @sql nvarchar(MAX) 

IterationStart:
SET @sql = '' 

SELECT TOP 5 @sql = @sql + 'ALTER TABLE ' + QUOTENAME(RC.CONSTRAINT_SCHEMA) 
    + '.' + QUOTENAME(KCU1.TABLE_NAME) 
    + ' DROP CONSTRAINT ' + QUOTENAME(rc.CONSTRAINT_NAME) + '; ' 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

IF @SQL <> ''
BEGIN
  EXEC(@SQL)
  GOTO IterationStart
END

OriginalL'auteur alik

J'ai utilisé le INFORMATION_SCHEMA solution mentionnée par @Yaroslav, mais il avait trop de clé étrangère constantes dans ma base de données pour s'adapter à tous dans un varchar(MAX). J'ai donc dû modifier le script pour utiliser une table temporaire et un curseur à la place.

Aussi, j'ai ajouté [] autour de le nom de la table.

DECLARE @SQL TABLE (Command VARCHAR(MAX))

INSERT @SQL
SELECT 'ALTER TABLE [' + FK.TABLE_NAME + '] DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)
  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
 INNER JOIN (
            SELECT i1.TABLE_NAME, i2.COLUMN_NAME
              FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
             INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME

DECLARE cmdCursor CURSOR
    FOR SELECT Command FROM @SQL
OPEN cmdCursor
DECLARE @Command VARCHAR(MAX)

FETCH NEXT FROM cmdCursor INTO @Command
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Command
    EXEC (@Command)
    FETCH NEXT FROM cmdCursor INTO @Command
END

CLOSE cmdCursor;
DEALLOCATE cmdCursor;

OriginalL'auteur egeskov