Question Comment puis-je lister toutes les clés étrangères référençant une table donnée dans SQL Server?


J'ai besoin de supprimer une table hautement référencée dans une base de données SQL Server. Comment puis-je obtenir une liste de toutes les contraintes de clé étrangère que je devrai supprimer pour faire tomber la table?

(Les réponses SQL sont préférables en cliquant sur dans l'interface graphique du studio de gestion.)


564
2018-01-27 12:17


origine


Réponses:


Je ne sais pas pourquoi personne n'a suggéré mais j'utilise sp_fkeys pour interroger des clés étrangères pour une table donnée:

EXEC sp_fkeys 'TableName'

863
2017-10-18 13:53



J'utiliserais la fonctionnalité de création de diagrammes de base de données dans SQL Server Management Studio, mais puisque vous l'avez exclu - cela a fonctionné pour moi dans SQL Server 2008 (ne pas avoir 2005).

Pour obtenir la liste des noms de tables et de colonnes référents ...

select 
    t.name as TableWithForeignKey, 
    fk.constraint_column_id as FK_PartNo, c.
    name as ForeignKeyColumn 
from 
    sys.foreign_key_columns as fk
inner join 
    sys.tables as t on fk.parent_object_id = t.object_id
inner join 
    sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where 
    fk.referenced_object_id = (select object_id 
                               from sys.tables 
                               where name = 'TableOthersForeignKeyInto')
order by 
    TableWithForeignKey, FK_PartNo

Pour obtenir les noms des contraintes de clé étrangère

select distinct name from sys.objects where object_id in 
(   select fk.constraint_object_id from sys.foreign_key_columns as fk
    where fk.referenced_object_id = 
        (select object_id from sys.tables where name = 'TableOthersForeignKeyInto')
)

193
2018-01-27 12:43



Cela vous donne:

  • Le FK lui-même
  • Schéma auquel appartient le FK
  • Le "table de référencement"ou la table qui a le FK
  • Le "colonne de référencement"ou la colonne à l'intérieur du tableau de référence qui pointe vers le FK
  • Le "table référencée"ou la table contenant la colonne clé que votre FK pointe vers
  • Le "colonne référencée"ou la colonne qui est la clé que votre FK pointe vers

Code ci-dessous:

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

133
2017-09-21 07:14



Essaye ça :

sp_help 'TableName'

121
2018-01-27 12:41



Vous devriez également faire attention aux références à d'autres objets.

Si la table a été fortement référencée par d'autres tables, elle est probablement aussi fortement référencée par d'autres objets tels que des vues, des procédures stockées, des fonctions, etc.

Je recommande vraiment un outil graphique tel que la boîte de dialogue 'voir les dépendances' dans SSMS ou un outil gratuit comme ApexSQL Search pour cela, car la recherche de dépendances dans d'autres objets peut être sujette à erreur si vous voulez le faire uniquement avec SQL.

Si SQL est la seule option, vous pouvez essayer de le faire comme ça.

select O.name as [Object_Name], C.text as [Object_Definition]
from sys.syscomments C
inner join sys.all_objects O ON C.id = O.object_id
where C.text like '%table_name%'

45
2018-04-01 09:10



La question initiale a demandé d'obtenir une liste de toutes les clés étrangères dans un tableau hautement référencé afin que la table puisse être supprimée.

Cette petite requête renvoie toutes les commandes 'drop foreign key' nécessaires pour déposer toutes les clés étrangères dans une table particulière:

SELECT 
   'ALTER TABLE ['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' '[DropCommand]'
FROM sys.foreign_key_columns fk
    JOIN sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id
    JOIN sys.schemas sch ON referencingTable.schema_id = sch.schema_id
    JOIN sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id
    JOIN sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id
WHERE referencedTable.name = 'MyTableName'

Exemple de sortie:

[DropCommand]
ALTER TABLE [dbo].[OtherTable1] DROP CONSTRAINT [FK_OtherTable1_MyTable]
ALTER TABLE [dbo].[OtherTable2] DROP CONSTRAINT [FK_OtherTable2_MyTable]

Omettez la clause WHERE pour obtenir les commandes de suppression pour toutes les clés étrangères dans la base de données en cours.


17
2018-02-12 00:23



SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
       PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
       FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
       -- Force the column to be non-nullable (see SQL BU 325751)
       --KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
       PK_NAME = CONVERT(SYSNAME,I.NAME),
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE
FROM   SYS.ALL_OBJECTS O1,
       SYS.ALL_OBJECTS O2,
       SYS.ALL_COLUMNS C1,
       SYS.ALL_COLUMNS C2,
       SYS.FOREIGN_KEYS F
       INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
         ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
       INNER JOIN SYS.INDEXES I
         ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
             AND F.KEY_INDEX_ID = I.INDEX_ID)
WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
       AND C2.COLUMN_ID = K.PARENT_COLUMN_ID

12
2017-08-06 13:52



Voici le code SQL que j'utiliserais.

SELECT 
   f.name AS 'Name of Foreign Key',
   OBJECT_NAME(f.parent_object_id) AS 'Table name',
   COL_NAME(fc.parent_object_id,fc.parent_column_id) AS 'Fieldname',
   OBJECT_NAME(t.object_id) AS 'References Table name',
   COL_NAME(t.object_id,fc.referenced_column_id) AS 'References fieldname',

   'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + ']  DROP CONSTRAINT [' + f.name + ']' AS 'Delete foreign key',

   'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + ']  WITH NOCHECK ADD CONSTRAINT [' + 
        f.name + '] FOREIGN KEY([' + COL_NAME(fc.parent_object_id,fc.parent_column_id) + ']) REFERENCES ' + 
        '[' + OBJECT_NAME(t.object_id) + '] ([' +
        COL_NAME(t.object_id,fc.referenced_column_id) + '])' AS 'Create foreign key'
    -- , delete_referential_action_desc AS 'UsesCascadeDelete'
FROM sys.foreign_keys AS f,
     sys.foreign_key_columns AS fc,
     sys.tables t 
WHERE f.OBJECT_ID = fc.constraint_object_id
AND t.OBJECT_ID = fc.referenced_object_id
AND OBJECT_NAME(t.object_id) = 'Employees'      --  Just show the FKs which reference a particular table
ORDER BY 2

Ce n'est pas particulièrement clair SQL, alors regardons un exemple.

Donc, en supposant que je voulais laisser tomber le Employees table dans le bien-aimé de Microsoft Northwind base de données, mais SQL Server m'a dit qu'une ou plusieurs clés étrangères m'empêchaient de le faire.

La commande SQL ci-dessus retournera ces résultats ...

Foreign Keyes

Il me montre qu'il y a 3 clés étrangères qui font référence à Employees table. En d'autres termes, je ne serais pas autorisé à supprimer (supprimer) cette table tant que ces trois clés étrangères n'auront pas été supprimées.

Dans les résultats, la première ligne est comment la contrainte de clé étrangère suivante serait montrée dans les résultats.

ALTER TABLE [dbo].[Employees]  WITH NOCHECK 
ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employees] ([EmployeeID])

L'avant-dernière colonne montre la commande SQL que je devrais utiliser pour effacer une de ces clés étrangères, par exemple:

ALTER TABLE [Employees] DROP CONSTRAINT [FK_Employees_Employees]

... et la colonne de droite montre le SQL à créer il...

ALTER TABLE [Employees] WITH NOCHECK 
ADD CONSTRAINT [FK_Employees_Employees] 
FOREIGN KEY([ReportsTo]) REFERENCES [Employees] ([EmployeeID])

Avec toutes ces commandes, vous avez tout ce dont vous avez besoin pour supprimer les clés étrangères pertinentes pour vous permettre de supprimer une table, puis les recréer plus tard.

Phew. J'espère que cela t'aides.


12
2018-05-03 09:35



SELECT
  object_name(parent_object_id),
  object_name(referenced_object_id),
  name 
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Table Name')

9
2017-11-04 03:50