How can I list all foreign keys referencing a given table in SQL Server

Question

I need to remove a highly referenced table in a SQL Server database   How can I get a list of all the foreign key constraints I will need to remove in order to drop the table    SQL answers preferable over clicking about in the GUI of the management studio

User · Answer

I d use the Database Diagramming feature in SQL Server Management Studio  but since you ruled that out - this worked for me in SQL Server 2008  don t have 2005    To get list of referring table and column names     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   To get names of foreign key constraints  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

User · Answer

I d use the Database Diagramming feature in SQL Server Management Studio  but since you ruled that out - this worked for me in SQL Server 2008  don t have 2005    To get list of referring table and column names     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   To get names of foreign key constraints  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

User · Answer

Try this    sp help  TableName

User · Answer

This gives you    The FK itself itself Schema that the FK belongs to The  referencing table  or the table that has the FK The  referencing column  or the column inside referencing table that points to the FK The  referenced table  or the table that has the key column that your FK is pointing to The  referenced column  or the column that is the key that your FK is pointing to   Code below   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

User · Answer

The original question asked to get a list of all foreign keys into a highly referenced table so that the table can be removed   This little query returns all the  drop foreign key  commands needed to drop all foreign keys into a particular table   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    Example output    DropCommand  ALTER TABLE  dbo   OtherTable1  DROP CONSTRAINT  FK OtherTable1 MyTable  ALTER TABLE  dbo   OtherTable2  DROP CONSTRAINT  FK OtherTable2 MyTable    Omit the WHERE-clause to get the drop commands for all foreign keys in the current database

User · Answer

The original question asked to get a list of all foreign keys into a highly referenced table so that the table can be removed   This little query returns all the  drop foreign key  commands needed to drop all foreign keys into a particular table   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    Example output    DropCommand  ALTER TABLE  dbo   OtherTable1  DROP CONSTRAINT  FK OtherTable1 MyTable  ALTER TABLE  dbo   OtherTable2  DROP CONSTRAINT  FK OtherTable2 MyTable    Omit the WHERE-clause to get the drop commands for all foreign keys in the current database

User · Answer

This gives you    The FK itself itself Schema that the FK belongs to The  referencing table  or the table that has the FK The  referencing column  or the column inside referencing table that points to the FK The  referenced table  or the table that has the key column that your FK is pointing to The  referenced column  or the column that is the key that your FK is pointing to   Code below   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

User · Answer

SELECT OBJECT NAME fk parent object id  as ReferencingTable           OBJECT NAME fk constraint object id  as  FKContraint    FROM sys foreign key columns as fk  WHERE fk referenced object id   OBJECT ID  ReferencedTable    U     This only shows the relationship if the are foreign key constraints  My database apparently predates the FK constraint Some table use triggers to enforce referential integrity  and sometimes there s nothing but a similarly named column to indicate the relationship  and no referential integrity at all    Fortunately  we do have a consistent naming scene so I am able to find referencing tables  and views like this   SELECT OBJECT NAME object id  from sys columns where name like  client id    I used this select as the basis for generating a script the does what I need to do on the related tables

User · Answer

Not sure why no one suggested but I use sp fkeys to query foreign keys for a given table   EXEC sp fkeys  TableName    You can also specify the schema   EXEC sp fkeys  pktable name    TableName    pktable owner    dbo    Without specifying the schema  the docs state the following      If pktable owner is not specified  the default table visibility rules   of the underlying DBMS apply       In SQL Server  if the current user owns a table with the specified   name  that table s columns are returned  If pktable owner is not   specified and the current user does not own a table with the specified   pktable name  the procedure looks for a table with the specified   pktable name owned by the database owner  If one exists  that table s   columns are returned

User · Answer

I have been using this on 2008 and up   It s similar to some other solutions listed but  the field names are proper cased to handle case specific  LatBin  collations   Additionally  you can feed it a single table name and retrieve just the info for that table    -- gt  gt SPECIFY THE DESIRED DB USE     GO                                                                                                      LIST OUT ALL PRIMARY AND FOREIGN KEY CONSTRAINTS IN A DB OR FOR A SPECIFIED TABLE                                                                                                 DECLARE  tblName VARCHAR 255                               SET  tblName   NULL-- gt NULL will return all PK FK constraints for every table in the database                         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  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        AND     O1 name    tblName              OR O2 name    tblName             OR  tblName IS null  ORDER BY PKTABLE NAME FKTABLE NAME

User · Answer

Also try   EXEC sp fkeys  tableName    schemaName    with sp fkeys you may filter the result by not only pk table name and schema but also with fk table name and schema  link

User · Answer

Working off of what  Gishu did I was able to produce and use the following SQL in SQL Server 2005    SELECT t name AS TableWithForeignKey  fk constraint column id AS FK PartNo          c name AS ForeignKeyColumn  o name AS FK Name    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        INNER JOIN sys objects AS o ON fk constraint object id   o object id   WHERE fk referenced object id    SELECT object id FROM sys tables                                          WHERE name    TableOthersForeignKeyInto     ORDER BY TableWithForeignKey  FK PartNo    Which Displays the tables  columns and Foreign Key names all in 1 query

User · Answer

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

User · Answer

Try this    sp help  TableName

User · Answer

The most Simplest one is by using sys foreign keys columns in SQL  Here the table contains the Object ids of all the foreign keys wrt their Referenced column ID Referenced Table ID as well as the Referencing Columns and Tables  As the Id s remains constant the result will be reliable for further modifications in Schema as well as tables   Query    SELECT     OBJECT NAME fkeys constraint object id  foreign key name  OBJECT NAME fkeys parent object id  referencing table name  COL NAME fkeys parent object id  fkeys parent column id  referencing column name  OBJECT SCHEMA NAME fkeys parent object id  referencing schema name  OBJECT NAME  fkeys referenced object id  referenced table name  COL NAME fkeys referenced object id  fkeys referenced column id   referenced column name  OBJECT SCHEMA NAME fkeys referenced object id  referenced schema name FROM sys foreign key columns AS fkeys   We can also add filter by using  where   WHERE OBJECT NAME fkeys parent object id     table name  AND  OBJECT SCHEMA NAME fkeys parent object id     schema name

User · Answer

You should also mind the references to other objects    If the table was highly referenced by other tables than it   s probably also highly referenced by other objects such as views  stored procedures  functions and more   I   d really recommend GUI tool such as    view dependencies    dialog in SSMS or free tool like ApexSQL Search for this because searching for dependencies in other objects can be error prone if you want to do it only with SQL   If SQL is the only option you could try doing it like this   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

User · Answer

Working off of what  Gishu did I was able to produce and use the following SQL in SQL Server 2005    SELECT t name AS TableWithForeignKey  fk constraint column id AS FK PartNo          c name AS ForeignKeyColumn  o name AS FK Name    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        INNER JOIN sys objects AS o ON fk constraint object id   o object id   WHERE fk referenced object id    SELECT object id FROM sys tables                                          WHERE name    TableOthersForeignKeyInto     ORDER BY TableWithForeignKey  FK PartNo    Which Displays the tables  columns and Foreign Key names all in 1 query

User · Answer

Here s the SQL code I would use   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   It s not particularly clear SQL  so let s look at an example   So  supposing I wanted to drop the Employees table in Microsoft s beloved Northwind database  but SQL Server told me that one or more Foreign Keys were preventing me from doing this   The SQL command above would return these results       It shows me that there are 3 Foreign Keys which reference the Employees table   In other words  I wouldn t be allowed to delete  drop  this table until these three Foreign Keys are first deleted   In the results  the first row is how the following Foreign Key constraint would be shown in the results   ALTER TABLE  dbo   Employees   WITH NOCHECK  ADD CONSTRAINT  FK Employees Employees  FOREIGN KEY  ReportsTo   REFERENCES  dbo   Employees    EmployeeID     The second-to-last column shows the SQL command I would need to use to delete one of these Foreign Keys  eg   ALTER TABLE  Employees  DROP CONSTRAINT  FK Employees Employees       and the right-hand column shows the SQL to create it     ALTER TABLE  Employees  WITH NOCHECK  ADD CONSTRAINT  FK Employees Employees   FOREIGN KEY  ReportsTo   REFERENCES  Employees    EmployeeID     With all of these commands  you have everything you need to delete the relevant Foreign Keys to allow you to delete a table  then recreate them later   Phew   Hope this helps

User · Answer

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

User · Answer

This gets any foreign key that involves the chosen table    Assumes a  FIRSTABLENAME SECONDTABLENAME format    declare  tablename as varchar MAX   SET  tablename    yourtablename   SELECT name  FROM YOURDATABASE sys objects  WHERE type desc    FOREIGN KEY CONSTRAINT  and  name LIKE         tablename    empdb    or name LIKE         tablename     This is a more general form    SELECT name  FROM YOURDATABASE PROD sys objects  WHERE type desc    FOREIGN KEY CONSTRAINT  and name LIKE        tablename       and  name NOT LIKE   a-zA-Z0-9      tablename       and name NOT LIKE        tablename     a-zA-Z0-9

User · Answer

Mysql server has information schema REFERENTIAL CONSTRAINTS table FYI  you can filter it by table name or referenced table name

User · Answer

I d use the Database Diagramming feature in SQL Server Management Studio  but since you ruled that out - this worked for me in SQL Server 2008  don t have 2005    To get list of referring table and column names     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   To get names of foreign key constraints  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

User · Answer

I am using this script to find all details related to foreign key   I am using INFORMATION SCHEMA  Below is a SQL Script   SELECT      ccu table name AS SourceTable      ccu constraint name AS SourceConstraint      ccu column name AS SourceColumn      kcu table name AS TargetTable      kcu column name AS TargetColumn FROM INFORMATION SCHEMA CONSTRAINT COLUMN USAGE ccu     INNER JOIN INFORMATION SCHEMA REFERENTIAL CONSTRAINTS rc         ON ccu CONSTRAINT NAME   rc CONSTRAINT NAME      INNER JOIN INFORMATION SCHEMA KEY COLUMN USAGE kcu          ON kcu CONSTRAINT NAME   rc UNIQUE CONSTRAINT NAME   ORDER BY ccu table name

User · Answer

Try this    sp help  TableName

User · Answer

I d use the Database Diagramming feature in SQL Server Management Studio  but since you ruled that out - this worked for me in SQL Server 2008  don t have 2005    To get list of referring table and column names     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   To get names of foreign key constraints  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

User · Answer

Mysql server has information schema REFERENTIAL CONSTRAINTS table FYI  you can filter it by table name or referenced table name

User · Answer

The following solution work for me   --Eliminar las llaves foraneas declare  query varchar 8000  declare cursorRecorrerTabla cursor for  SELECT   ALTER TABLE  PoaComFinH     sch name       referencingTable Name    DROP CONSTRAINT    foreignKey name      query  FROM PoaComFinH sys foreign key columns fk JOIN PoaComFinH sys tables referencingTable ON fk parent object id   referencingTable object id JOIN PoaComFinH sys schemas sch ON referencingTable schema id   sch schema id JOIN PoaComFinH sys objects foreignKey ON foreignKey object id   fk constraint object id JOIN PoaComFinH sys tables referencedTable ON fk referenced object id   referencedTable object id   --3ro  abrir el cursor  open cursorRecorrerTabla fetch next from cursorRecorrerTabla into  query while   fetch status   0 begin --inicio cuerpo del cursor     print  query     exec  query  --fin cuerpo del cursor fetch next from cursorRecorrerTabla into  query end --cerrar cursor close cursorRecorrerTabla deallocate cursorRecorrerTabla

User · Answer

Most preferable answer by  BankZ   sp help  TableName       additionally for different schema  sp help  schemaName TableName

User · Answer

Most preferable answer by  BankZ   sp help  TableName       additionally for different schema  sp help  schemaName TableName

User · Answer

SELECT   object name parent object id     object name referenced object id     name  FROM sys foreign keys WHERE parent object id   object id  Table Name

User · Answer

This gets any foreign key that involves the chosen table    Assumes a  FIRSTABLENAME SECONDTABLENAME format    declare  tablename as varchar MAX   SET  tablename    yourtablename   SELECT name  FROM YOURDATABASE sys objects  WHERE type desc    FOREIGN KEY CONSTRAINT  and  name LIKE         tablename    empdb    or name LIKE         tablename     This is a more general form    SELECT name  FROM YOURDATABASE PROD sys objects  WHERE type desc    FOREIGN KEY CONSTRAINT  and name LIKE        tablename       and  name NOT LIKE   a-zA-Z0-9      tablename       and name NOT LIKE        tablename     a-zA-Z0-9

User · Answer

I have been using this on 2008 and up   It s similar to some other solutions listed but  the field names are proper cased to handle case specific  LatBin  collations   Additionally  you can feed it a single table name and retrieve just the info for that table    -- gt  gt SPECIFY THE DESIRED DB USE     GO                                                                                                      LIST OUT ALL PRIMARY AND FOREIGN KEY CONSTRAINTS IN A DB OR FOR A SPECIFIED TABLE                                                                                                 DECLARE  tblName VARCHAR 255                               SET  tblName   NULL-- gt NULL will return all PK FK constraints for every table in the database                         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  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        AND     O1 name    tblName              OR O2 name    tblName             OR  tblName IS null  ORDER BY PKTABLE NAME FKTABLE NAME

User · Answer

There is how to get count of all responsibilities for selected Id  Just change  dbTableName value   dbRowId value and its type  if int you need to remove    in line no 82    SET  SQL         Enjoy   DECLARE  dbTableName varchar max     User  DECLARE  dbRowId uniqueidentifier    21d34ecd-c1fd-11e2-8545-002219a42e1c   DECLARE  FK ROWCOUNT int DECLARE  SQL nvarchar max   DECLARE  PKTABLE QUALIFIER sysname DECLARE  PKTABLE OWNER sysname DECLARE  PKTABLE NAME sysname DECLARE  PKCOLUMN NAME sysname DECLARE  FKTABLE QUALIFIER sysname DECLARE  FKTABLE OWNER sysname DECLARE  FKTABLE NAME sysname DECLARE  FKCOLUMN NAME sysname DECLARE  UPDATE RULE smallint DECLARE  DELETE RULE smallint DECLARE  FK NAME sysname DECLARE  PK NAME sysname DECLARE  DEFERRABILITY sysname  IF OBJECT ID  tempdb   Temp1   IS NOT NULL     DROP TABLE  Temp1  CREATE TABLE  Temp1        PKTABLE QUALIFIER sysname      PKTABLE OWNER sysname      PKTABLE NAME sysname      PKCOLUMN NAME sysname      FKTABLE QUALIFIER sysname      FKTABLE OWNER sysname      FKTABLE NAME sysname      FKCOLUMN NAME sysname      UPDATE RULE smallint      DELETE RULE smallint      FK NAME sysname      PK NAME sysname      DEFERRABILITY sysname      FK ROWCOUNT int        DECLARE FK Counter Cursor CURSOR FOR     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            AND O1 NAME    dbTableName OPEN FK Counter Cursor  FETCH NEXT FROM FK Counter Cursor INTO  PKTABLE QUALIFIER   PKTABLE OWNER   PKTABLE NAME   PKCOLUMN NAME   FKTABLE QUALIFIER   FKTABLE OWNER   FKTABLE NAME   FKCOLUMN NAME   UPDATE RULE   DELETE RULE   FK NAME   PK NAME   DEFERRABILITY  WHILE   FETCH STATUS   0    BEGIN         SET  SQL    SELECT  dbCountOut   COUNT    FROM       FKTABLE NAME      WHERE       FKCOLUMN NAME              CAST  dbRowId AS varchar max                   EXECUTE sp executesql  SQL  N  dbCountOut int OUTPUT    dbCountOut    FK ROWCOUNT OUTPUT          INSERT INTO  Temp1  PKTABLE QUALIFIER  PKTABLE OWNER  PKTABLE NAME  PKCOLUMN NAME  FKTABLE QUALIFIER  FKTABLE OWNER  FKTABLE NAME  FKCOLUMN NAME  UPDATE RULE  DELETE RULE  FK NAME  PK NAME  DEFERRABILITY  FK ROWCOUNT  VALUES   FKTABLE QUALIFIER   PKTABLE OWNER   PKTABLE NAME   PKCOLUMN NAME   FKTABLE QUALIFIER   FKTABLE OWNER   FKTABLE NAME   FKCOLUMN NAME   UPDATE RULE   DELETE RULE   FK NAME   PK NAME   DEFERRABILITY   FK ROWCOUNT        FETCH NEXT FROM FK Counter Cursor INTO  PKTABLE QUALIFIER   PKTABLE OWNER   PKTABLE NAME   PKCOLUMN NAME   FKTABLE QUALIFIER   FKTABLE OWNER   FKTABLE NAME   FKCOLUMN NAME   UPDATE RULE   DELETE RULE   FK NAME   PK NAME   DEFERRABILITY     END  CLOSE FK Counter Cursor  DEALLOCATE FK Counter Cursor  GO SELECT   FROM  Temp1 GO

User · Answer

Some good answers above  But I prefer to have the answer with one query  This piece of code is taken from sys sp helpconstraint  sys proc    That s the way Microsoft looks up if there are foreign keys associated to the tbl    --setup variables  Just change  Customer  to tbl you want declare  objid int       objname nvarchar 776  select  objname    Customer      select  objid   object id  objname   if exists  select   from sys foreign keys where referenced object id    objid      select  Table is referenced by foreign key            db name                   rtrim schema name ObjectProperty parent object id  schemaid                     object name parent object id                   object name object id      from sys foreign keys      where referenced object id    objid      order by 1   The answer will look like this  test db name dbo Account  FK Account Customer

User · Answer

There is how to get count of all responsibilities for selected Id  Just change  dbTableName value   dbRowId value and its type  if int you need to remove    in line no 82    SET  SQL         Enjoy   DECLARE  dbTableName varchar max     User  DECLARE  dbRowId uniqueidentifier    21d34ecd-c1fd-11e2-8545-002219a42e1c   DECLARE  FK ROWCOUNT int DECLARE  SQL nvarchar max   DECLARE  PKTABLE QUALIFIER sysname DECLARE  PKTABLE OWNER sysname DECLARE  PKTABLE NAME sysname DECLARE  PKCOLUMN NAME sysname DECLARE  FKTABLE QUALIFIER sysname DECLARE  FKTABLE OWNER sysname DECLARE  FKTABLE NAME sysname DECLARE  FKCOLUMN NAME sysname DECLARE  UPDATE RULE smallint DECLARE  DELETE RULE smallint DECLARE  FK NAME sysname DECLARE  PK NAME sysname DECLARE  DEFERRABILITY sysname  IF OBJECT ID  tempdb   Temp1   IS NOT NULL     DROP TABLE  Temp1  CREATE TABLE  Temp1        PKTABLE QUALIFIER sysname      PKTABLE OWNER sysname      PKTABLE NAME sysname      PKCOLUMN NAME sysname      FKTABLE QUALIFIER sysname      FKTABLE OWNER sysname      FKTABLE NAME sysname      FKCOLUMN NAME sysname      UPDATE RULE smallint      DELETE RULE smallint      FK NAME sysname      PK NAME sysname      DEFERRABILITY sysname      FK ROWCOUNT int        DECLARE FK Counter Cursor CURSOR FOR     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            AND O1 NAME    dbTableName OPEN FK Counter Cursor  FETCH NEXT FROM FK Counter Cursor INTO  PKTABLE QUALIFIER   PKTABLE OWNER   PKTABLE NAME   PKCOLUMN NAME   FKTABLE QUALIFIER   FKTABLE OWNER   FKTABLE NAME   FKCOLUMN NAME   UPDATE RULE   DELETE RULE   FK NAME   PK NAME   DEFERRABILITY  WHILE   FETCH STATUS   0    BEGIN         SET  SQL    SELECT  dbCountOut   COUNT    FROM       FKTABLE NAME      WHERE       FKCOLUMN NAME              CAST  dbRowId AS varchar max                   EXECUTE sp executesql  SQL  N  dbCountOut int OUTPUT    dbCountOut    FK ROWCOUNT OUTPUT          INSERT INTO  Temp1  PKTABLE QUALIFIER  PKTABLE OWNER  PKTABLE NAME  PKCOLUMN NAME  FKTABLE QUALIFIER  FKTABLE OWNER  FKTABLE NAME  FKCOLUMN NAME  UPDATE RULE  DELETE RULE  FK NAME  PK NAME  DEFERRABILITY  FK ROWCOUNT  VALUES   FKTABLE QUALIFIER   PKTABLE OWNER   PKTABLE NAME   PKCOLUMN NAME   FKTABLE QUALIFIER   FKTABLE OWNER   FKTABLE NAME   FKCOLUMN NAME   UPDATE RULE   DELETE RULE   FK NAME   PK NAME   DEFERRABILITY   FK ROWCOUNT        FETCH NEXT FROM FK Counter Cursor INTO  PKTABLE QUALIFIER   PKTABLE OWNER   PKTABLE NAME   PKCOLUMN NAME   FKTABLE QUALIFIER   FKTABLE OWNER   FKTABLE NAME   FKCOLUMN NAME   UPDATE RULE   DELETE RULE   FK NAME   PK NAME   DEFERRABILITY     END  CLOSE FK Counter Cursor  DEALLOCATE FK Counter Cursor  GO SELECT   FROM  Temp1 GO

User · Answer

Not sure why no one suggested but I use sp fkeys to query foreign keys for a given table   EXEC sp fkeys  TableName    You can also specify the schema   EXEC sp fkeys  pktable name    TableName    pktable owner    dbo    Without specifying the schema  the docs state the following      If pktable owner is not specified  the default table visibility rules   of the underlying DBMS apply       In SQL Server  if the current user owns a table with the specified   name  that table s columns are returned  If pktable owner is not   specified and the current user does not own a table with the specified   pktable name  the procedure looks for a table with the specified   pktable name owned by the database owner  If one exists  that table s   columns are returned

User · Answer

The most Simplest one is by using sys foreign keys columns in SQL  Here the table contains the Object ids of all the foreign keys wrt their Referenced column ID Referenced Table ID as well as the Referencing Columns and Tables  As the Id s remains constant the result will be reliable for further modifications in Schema as well as tables   Query    SELECT     OBJECT NAME fkeys constraint object id  foreign key name  OBJECT NAME fkeys parent object id  referencing table name  COL NAME fkeys parent object id  fkeys parent column id  referencing column name  OBJECT SCHEMA NAME fkeys parent object id  referencing schema name  OBJECT NAME  fkeys referenced object id  referenced table name  COL NAME fkeys referenced object id  fkeys referenced column id   referenced column name  OBJECT SCHEMA NAME fkeys referenced object id  referenced schema name FROM sys foreign key columns AS fkeys   We can also add filter by using  where   WHERE OBJECT NAME fkeys parent object id     table name  AND  OBJECT SCHEMA NAME fkeys parent object id     schema name

User · Answer

You can find through below query            SELECT OBJECT NAME  FK referenced object id   Referenced Table          OBJECT NAME FK parent object id   Referring Table   FK name  Foreign Key          COL NAME FK referenced object id  FKC referenced column id   Referenced Column         COL NAME FK parent object id FKC parent column id   Referring Column       FROM sys foreign keys AS FK              INNER JOIN sys foreign key columns AS FKC                   ON FKC constraint object id   FK OBJECT ID      WHERE OBJECT NAME  FK referenced object id     YourTableName       AND COL NAME FK referenced object id  FKC referenced column id     YourColumnName       order by  OBJECT NAME FK parent object id

User · Answer

SELECT   object name parent object id     object name referenced object id     name  FROM sys foreign keys WHERE parent object id   object id  Table Name

User · Answer

SELECT OBJECT NAME parent object id   Parent table   c NAME  Parent column name   OBJECT NAME referenced object id   Referenced table   cref NAME  Referenced column name  FROM  sys foreign key columns fkc  INNER JOIN  sys columns c     ON fkc parent column id   c column id        AND fkc parent object id   c object id INNER JOIN  sys columns cref     ON fkc referenced column id   cref column id        AND fkc referenced object id   cref object id  where   OBJECT NAME parent object id     tablename    If you want to get the foreign key relation of all the tables exclude the where clause else write your tablename instead of tablename

User · Answer

Some good answers above  But I prefer to have the answer with one query  This piece of code is taken from sys sp helpconstraint  sys proc    That s the way Microsoft looks up if there are foreign keys associated to the tbl    --setup variables  Just change  Customer  to tbl you want declare  objid int       objname nvarchar 776  select  objname    Customer      select  objid   object id  objname   if exists  select   from sys foreign keys where referenced object id    objid      select  Table is referenced by foreign key            db name                   rtrim schema name ObjectProperty parent object id  schemaid                     object name parent object id                   object name object id      from sys foreign keys      where referenced object id    objid      order by 1   The answer will look like this  test db name dbo Account  FK Account Customer

User · Answer

Try this    sp help  TableName

User · Answer

I am using this script to find all details related to foreign key   I am using INFORMATION SCHEMA  Below is a SQL Script   SELECT      ccu table name AS SourceTable      ccu constraint name AS SourceConstraint      ccu column name AS SourceColumn      kcu table name AS TargetTable      kcu column name AS TargetColumn FROM INFORMATION SCHEMA CONSTRAINT COLUMN USAGE ccu     INNER JOIN INFORMATION SCHEMA REFERENTIAL CONSTRAINTS rc         ON ccu CONSTRAINT NAME   rc CONSTRAINT NAME      INNER JOIN INFORMATION SCHEMA KEY COLUMN USAGE kcu          ON kcu CONSTRAINT NAME   rc UNIQUE CONSTRAINT NAME   ORDER BY ccu table name

User · Answer

Determine primary keys and unique keys for all tables in a database     This should list all the constraints and at the end you can put your filters     CAST IS DONE   SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT   WITH   ALL KEYS IN TABLE  CONSTRAINT NAME CONSTRAINT TYPE PARENT TABLE NAME PARENT COL NAME PARENT COL NAME DATA TYPE REFERENCE TABLE NAME REFERENCE COL NAME   AS   SELECT  CONSTRAINT NAME  CAST  PKnUKEY name AS VARCHAR 30             CONSTRAINT TYPE CAST  PKnUKEY type desc AS VARCHAR 30             PARENT TABLE NAME CAST  PKnUTable name AS VARCHAR 30             PARENT COL NAME CAST   PKnUKEYCol name AS VARCHAR 30             PARENT COL NAME DATA TYPE   oParentColDtl DATA TYPE                  REFERENCE TABLE NAME              REFERENCE COL NAME      FROM sys key constraints as PKnUKEY     INNER JOIN sys tables as PKnUTable             ON PKnUTable object id   PKnUKEY parent object id     INNER JOIN sys index columns as PKnUColIdx             ON PKnUColIdx object id   PKnUTable object id             AND PKnUColIdx index id   PKnUKEY unique index id     INNER JOIN sys columns as PKnUKEYCol             ON PKnUKEYCol object id   PKnUTable object id             AND PKnUKEYCol column id   PKnUColIdx column id      INNER JOIN INFORMATION SCHEMA COLUMNS oParentColDtl             ON oParentColDtl TABLE NAME PKnUTable name             AND oParentColDtl COLUMN NAME PKnUKEYCol name UNION ALL SELECT  CONSTRAINT NAME  CAST  oConstraint name AS VARCHAR 30             CONSTRAINT TYPE  FK           PARENT TABLE NAME CAST  oParent name AS VARCHAR 30             PARENT COL NAME CAST   oParentCol name AS VARCHAR 30             PARENT COL NAME DATA TYPE  oParentColDtl DATA TYPE               REFERENCE TABLE NAME CAST   oReference name AS VARCHAR 30             REFERENCE COL NAME CAST  oReferenceCol name AS VARCHAR 30    FROM sys foreign key columns FKC     INNER JOIN sys sysobjects oConstraint             ON FKC constraint object id oConstraint id      INNER JOIN sys sysobjects oParent             ON FKC parent object id oParent id     INNER JOIN sys all columns oParentCol             ON FKC parent object id oParentCol object id    ID of the object to which this column belongs                AND FKC parent column id oParentCol column id   ID of the column  Is unique within the object Column IDs might not be sequential        INNER JOIN sys sysobjects oReference             ON FKC referenced object id oReference id     INNER JOIN INFORMATION SCHEMA COLUMNS oParentColDtl             ON oParentColDtl TABLE NAME oParent name             AND oParentColDtl COLUMN NAME oParentCol name     INNER JOIN sys all columns oReferenceCol             ON FKC referenced object id oReferenceCol object id    ID of the object to which this column belongs                AND FKC referenced column id oReferenceCol column id   ID of the column  Is unique within the object Column IDs might not be sequential        select   from   ALL KEYS IN TABLE where        PARENT TABLE NAME  in   YOUR TABLE NAME        or REFERENCE TABLE NAME  in   YOUR TABLE NAME   ORDER BY PARENT TABLE NAME CONSTRAINT NAME    For reference please read thru - http   blogs msdn com b sqltips archive 2005 09 16 469136 aspx

User · Answer

Also try   EXEC sp fkeys  tableName    schemaName    with sp fkeys you may filter the result by not only pk table name and schema but also with fk table name and schema  link

User · Answer

List of all foreign keys referencing a given table in SQL Server     You can get the referencing table name and column name through following query     SELECT     OBJECT NAME f parent object id  TableName     COL NAME fc parent object id fc parent column id  ColName FROM     sys foreign keys AS f INNER JOIN     sys foreign key columns AS fc        ON f OBJECT ID   fc constraint object id INNER JOIN     sys tables t        ON t OBJECT ID   fc referenced object id WHERE     OBJECT NAME  f referenced object id     TableName    And following screenshot for your understanding

User · Answer

First  EXEC sp fkeys  Table    Schema    Then use NimbleText to play with your results

User · Answer

You can find through below query            SELECT OBJECT NAME  FK referenced object id   Referenced Table          OBJECT NAME FK parent object id   Referring Table   FK name  Foreign Key          COL NAME FK referenced object id  FKC referenced column id   Referenced Column         COL NAME FK parent object id FKC parent column id   Referring Column       FROM sys foreign keys AS FK              INNER JOIN sys foreign key columns AS FKC                   ON FKC constraint object id   FK OBJECT ID      WHERE OBJECT NAME  FK referenced object id     YourTableName       AND COL NAME FK referenced object id  FKC referenced column id     YourColumnName       order by  OBJECT NAME FK parent object id

User · Answer

You should also mind the references to other objects    If the table was highly referenced by other tables than it   s probably also highly referenced by other objects such as views  stored procedures  functions and more   I   d really recommend GUI tool such as    view dependencies    dialog in SSMS or free tool like ApexSQL Search for this because searching for dependencies in other objects can be error prone if you want to do it only with SQL   If SQL is the only option you could try doing it like this   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

User · Answer

SELECT OBJECT NAME fk parent object id  as ReferencingTable           OBJECT NAME fk constraint object id  as  FKContraint    FROM sys foreign key columns as fk  WHERE fk referenced object id   OBJECT ID  ReferencedTable    U     This only shows the relationship if the are foreign key constraints  My database apparently predates the FK constraint Some table use triggers to enforce referential integrity  and sometimes there s nothing but a similarly named column to indicate the relationship  and no referential integrity at all    Fortunately  we do have a consistent naming scene so I am able to find referencing tables  and views like this   SELECT OBJECT NAME object id  from sys columns where name like  client id    I used this select as the basis for generating a script the does what I need to do on the related tables

User · Answer

Determine primary keys and unique keys for all tables in a database     This should list all the constraints and at the end you can put your filters     CAST IS DONE   SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT   WITH   ALL KEYS IN TABLE  CONSTRAINT NAME CONSTRAINT TYPE PARENT TABLE NAME PARENT COL NAME PARENT COL NAME DATA TYPE REFERENCE TABLE NAME REFERENCE COL NAME   AS   SELECT  CONSTRAINT NAME  CAST  PKnUKEY name AS VARCHAR 30             CONSTRAINT TYPE CAST  PKnUKEY type desc AS VARCHAR 30             PARENT TABLE NAME CAST  PKnUTable name AS VARCHAR 30             PARENT COL NAME CAST   PKnUKEYCol name AS VARCHAR 30             PARENT COL NAME DATA TYPE   oParentColDtl DATA TYPE                  REFERENCE TABLE NAME              REFERENCE COL NAME      FROM sys key constraints as PKnUKEY     INNER JOIN sys tables as PKnUTable             ON PKnUTable object id   PKnUKEY parent object id     INNER JOIN sys index columns as PKnUColIdx             ON PKnUColIdx object id   PKnUTable object id             AND PKnUColIdx index id   PKnUKEY unique index id     INNER JOIN sys columns as PKnUKEYCol             ON PKnUKEYCol object id   PKnUTable object id             AND PKnUKEYCol column id   PKnUColIdx column id      INNER JOIN INFORMATION SCHEMA COLUMNS oParentColDtl             ON oParentColDtl TABLE NAME PKnUTable name             AND oParentColDtl COLUMN NAME PKnUKEYCol name UNION ALL SELECT  CONSTRAINT NAME  CAST  oConstraint name AS VARCHAR 30             CONSTRAINT TYPE  FK           PARENT TABLE NAME CAST  oParent name AS VARCHAR 30             PARENT COL NAME CAST   oParentCol name AS VARCHAR 30             PARENT COL NAME DATA TYPE  oParentColDtl DATA TYPE               REFERENCE TABLE NAME CAST   oReference name AS VARCHAR 30             REFERENCE COL NAME CAST  oReferenceCol name AS VARCHAR 30    FROM sys foreign key columns FKC     INNER JOIN sys sysobjects oConstraint             ON FKC constraint object id oConstraint id      INNER JOIN sys sysobjects oParent             ON FKC parent object id oParent id     INNER JOIN sys all columns oParentCol             ON FKC parent object id oParentCol object id    ID of the object to which this column belongs                AND FKC parent column id oParentCol column id   ID of the column  Is unique within the object Column IDs might not be sequential        INNER JOIN sys sysobjects oReference             ON FKC referenced object id oReference id     INNER JOIN INFORMATION SCHEMA COLUMNS oParentColDtl             ON oParentColDtl TABLE NAME oParent name             AND oParentColDtl COLUMN NAME oParentCol name     INNER JOIN sys all columns oReferenceCol             ON FKC referenced object id oReferenceCol object id    ID of the object to which this column belongs                AND FKC referenced column id oReferenceCol column id   ID of the column  Is unique within the object Column IDs might not be sequential        select   from   ALL KEYS IN TABLE where        PARENT TABLE NAME  in   YOUR TABLE NAME        or REFERENCE TABLE NAME  in   YOUR TABLE NAME   ORDER BY PARENT TABLE NAME CONSTRAINT NAME    For reference please read thru - http   blogs msdn com b sqltips archive 2005 09 16 469136 aspx

User · Answer

First  EXEC sp fkeys  Table    Schema    Then use NimbleText to play with your results

User · Answer

Here s the SQL code I would use   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   It s not particularly clear SQL  so let s look at an example   So  supposing I wanted to drop the Employees table in Microsoft s beloved Northwind database  but SQL Server told me that one or more Foreign Keys were preventing me from doing this   The SQL command above would return these results       It shows me that there are 3 Foreign Keys which reference the Employees table   In other words  I wouldn t be allowed to delete  drop  this table until these three Foreign Keys are first deleted   In the results  the first row is how the following Foreign Key constraint would be shown in the results   ALTER TABLE  dbo   Employees   WITH NOCHECK  ADD CONSTRAINT  FK Employees Employees  FOREIGN KEY  ReportsTo   REFERENCES  dbo   Employees    EmployeeID     The second-to-last column shows the SQL command I would need to use to delete one of these Foreign Keys  eg   ALTER TABLE  Employees  DROP CONSTRAINT  FK Employees Employees       and the right-hand column shows the SQL to create it     ALTER TABLE  Employees  WITH NOCHECK  ADD CONSTRAINT  FK Employees Employees   FOREIGN KEY  ReportsTo   REFERENCES  Employees    EmployeeID     With all of these commands  you have everything you need to delete the relevant Foreign Keys to allow you to delete a table  then recreate them later   Phew   Hope this helps

User · Answer

List of all foreign keys referencing a given table in SQL Server     You can get the referencing table name and column name through following query     SELECT     OBJECT NAME f parent object id  TableName     COL NAME fc parent object id fc parent column id  ColName FROM     sys foreign keys AS f INNER JOIN     sys foreign key columns AS fc        ON f OBJECT ID   fc constraint object id INNER JOIN     sys tables t        ON t OBJECT ID   fc referenced object id WHERE     OBJECT NAME  f referenced object id     TableName    And following screenshot for your understanding

User · Answer

SELECT OBJECT NAME parent object id   Parent table   c NAME  Parent column name   OBJECT NAME referenced object id   Referenced table   cref NAME  Referenced column name  FROM  sys foreign key columns fkc  INNER JOIN  sys columns c     ON fkc parent column id   c column id        AND fkc parent object id   c object id INNER JOIN  sys columns cref     ON fkc referenced column id   cref column id        AND fkc referenced object id   cref object id  where   OBJECT NAME parent object id     tablename    If you want to get the foreign key relation of all the tables exclude the where clause else write your tablename instead of tablename

User · Answer

The following solution work for me   --Eliminar las llaves foraneas declare  query varchar 8000  declare cursorRecorrerTabla cursor for  SELECT   ALTER TABLE  PoaComFinH     sch name       referencingTable Name    DROP CONSTRAINT    foreignKey name      query  FROM PoaComFinH sys foreign key columns fk JOIN PoaComFinH sys tables referencingTable ON fk parent object id   referencingTable object id JOIN PoaComFinH sys schemas sch ON referencingTable schema id   sch schema id JOIN PoaComFinH sys objects foreignKey ON foreignKey object id   fk constraint object id JOIN PoaComFinH sys tables referencedTable ON fk referenced object id   referencedTable object id   --3ro  abrir el cursor  open cursorRecorrerTabla fetch next from cursorRecorrerTabla into  query while   fetch status   0 begin --inicio cuerpo del cursor     print  query     exec  query  --fin cuerpo del cursor fetch next from cursorRecorrerTabla into  query end --cerrar cursor close cursorRecorrerTabla deallocate cursorRecorrerTabla

[sql] How can I list all foreign keys referencing a given table in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to tsql