How can I find out what FOREIGN KEY constraint references a table in SQL Server

Question

I am trying to drop a table but getting the following message      Msg 3726  Level 16  State 1  Line 3   Could not drop object  dbo UserProfile  because it is referenced by a FOREIGN KEY constraint    Msg 2714  Level 16  State 6  Line 2   There is already an object named  UserProfile  in the database    I looked around with SQL Server Management Studio but I am unable to find the constraint  How can I find out the foreign key constraints

User · Answer

In SQL Server Management Studio you can just right click the table in the  object explorer  and select  View Dependencies     This would give a you a good starting point   It shows tables  views  and procedures that reference the table

User · Answer

You can also return all the information about the Foreign Keys by adapating  LittleSweetSeas answer   SELECT     OBJECT NAME f parent object id  ConsTable     OBJECT NAME  f referenced object id  refTable     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 order by ConsTable

User · Answer

Here it is   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     YourTableName    This way  you ll get the referencing table and column name   Edited to use sys tables instead of generic sys objects as per comment suggestion  Thanks  marc s

User · Answer

You could use this query to display Foreign key constaraints   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 ---- optional  ORDER BY 1 2 3 4 WHERE PK TABLE NAME  YourTable    Taken from http   blog sqlauthority com 2006 11 01 sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database

User · Answer

--The following may give you more of what you re looking for   create Procedure spShowRelationShips          Table varchar 250    null       RelatedTable varchar 250    null   as begin     if  Table is null and  RelatedTable is null         select  object name k constraint object id  ForeginKeyName                   object name k Parent Object id  TableName                   object name k referenced object id  RelatedTable                   c Name RelatedColumnName                    object name rc object id          rc name RelatedKeyField         from sys foreign key columns k         left join sys columns c on object name c object id    object name k Parent Object id  and c column id   k parent column id         left join sys columns rc on object name rc object id    object name k referenced object id  and rc column id   k referenced column id         order by 2 3      if  Table is not null and  RelatedTable is null         select  object name k constraint object id  ForeginKeyName                   object name k Parent Object id  TableName                   object name k referenced object id  RelatedTable                   c Name RelatedColumnName                    object name rc object id          rc name RelatedKeyField         from sys foreign key columns k         left join sys columns c on object name c object id    object name k Parent Object id  and c column id   k parent column id         left join sys columns rc on object name rc object id    object name k referenced object id  and rc column id   k referenced column id         where object name k Parent Object id    Table         order by 2 3      if  Table is null and  RelatedTable is not null         select  object name k constraint object id  ForeginKeyName                   object name k Parent Object id  TableName                   object name k referenced object id  RelatedTable                   c Name RelatedColumnName                    object name rc object id          rc name RelatedKeyField         from sys foreign key columns k         left join sys columns c on object name c object id    object name k Parent Object id  and c column id   k parent column id         left join sys columns rc on object name rc object id    object name k referenced object id  and rc column id   k referenced column id         where object name k referenced object id    RelatedTable         order by 2 3    end

User · Answer

I found this answer quite simple and did the trick for what I needed  https   stackoverflow com a 12956348 652519  A summary from the link  use this query   EXEC sp fkeys  TableName    Quick and simple  I was able to locate all the foreign key tables  respective columns and foreign key names of 15 tables pretty quickly   As  mdisibio noted below  here s a link to the documentation that details the different parameters that can be used  https   docs microsoft com en-us sql relational-databases system-stored-procedures sp-fkeys-transact-sql

User · Answer

In Object Explorer  expand the table  and expand the Keys

User · Answer

The easiest way to get Primary Key and Foreign Key for a table is       Get primary key and foreign key for a table    USE DatabaseName   SELECT CONSTRAINT NAME FROM INFORMATION SCHEMA KEY COLUMN USAGE WHERE CONSTRAINT NAME LIKE  PK   AND TABLE NAME    TableName   SELECT CONSTRAINT NAME FROM INFORMATION SCHEMA KEY COLUMN USAGE WHERE CONSTRAINT NAME LIKE  FK   AND TABLE NAME    TableName

User · Answer

try the following query   select object name sfc constraint object id  AS constraint name         OBJECT Name parent object id  AS table name          ac1 name as table column name         OBJECT name referenced object id  as reference table name               ac2 name as reference column name from  sys foreign key columns sfc join sys all columns ac1 on  ac1 object id sfc parent object id and ac1 column id sfc parent column id  join sys all columns ac2 on  ac2 object id sfc referenced object id and ac2 column id sfc referenced column id   where sfc parent object id OBJECT ID  lt main table name gt      this will give the constraint name  column names which will be referring and tables which will be depending on the constraint will be there

User · Answer

Another way is to check the results of   sp help  TableName     or just highlight the quoted TableName and pres ALT F1   With time passing  I just decided to refine my answer  Below is a screenshot of the results that sp help provides  A have used the AdventureWorksDW2012 DB for this example  There is numerous good information there  and what we are looking for is at the very end - highlighted in green

User · Answer

if you want to go via SSMS on the object explorer window  right click on the object you want to drop  do view dependencies

User · Answer

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

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

Here is the best way  to find out Foreign Key Relationship in all Database   exec sp helpconstraint  Table Name    and one more way  select   from INFORMATION SCHEMA KEY COLUMN USAGE where TABLE NAME  Table Name  --and left CONSTRAINT NAME 2   FK  If you want single key

User · Answer

Try this  SELECT   object name parent object id  ParentTableName    object name referenced object id  RefTableName    name  FROM sys foreign keys WHERE parent object id   object id  Tablename

[sql] How can I find out what FOREIGN KEY constraint references a table in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008