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?

Engulf

I like sp_help 'dbo.TableName' See here for more ways: #483693 – Conservationist

Worth noticing: Answer by @LittleSweetSeas will return info about the foreign keys FOR a given referenced table, however @Gayathri-Varma 's answer details for a given parent table. Both are useful in different context and both win their own race :-) – Accidence

Answer 1

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

Aldana

You should use the more focused sys.tables rather than sys.objects – Insouciance

@marc_s: Thank u, but could you post an example? AFAIK in sys.tables i have no FK references – Aldana

What I meant it: just replace INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id with INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id – Insouciance

@Aldana i had run the above query still i am not getting the object_name and column name for the table which had a foreign key constraint – Staffard

You could beef up your select with a little more info: SELECT f.name ConstraintName, f.type_desc ConstraintType, OBJECT_NAME(f.parent_object_id) ConstrainedTable, COL_NAME(fc.parent_object_id, fc.parent_column_id) ConstrainedColumn, OBJECT_NAME(f.referenced_object_id) ReferencedTable, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferencedColumn – Posy

Joined table t is never referenced, and this works for me without the second inner join. Is it superfluous? Has a recent change made its use moot? – Azotobacter

Actually, joined table t is used to limit sys.foreign_key_columns table output. It may be superflous, but it depends on your schema. – Aldana

Answer 2

Another way is to check the results of

sp_help 'TableName'

(or just highlight the quoted TableName and press 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:

Countermark

+1 This gives a lot of helpful information and this shows foreign keys at the bottom of the output – Archaimbaud

This gives me a lot of info in the smallest number of lines of code – Venery

This is the coolest short-cut! Completely beats Ctl-R to refresh the schema! – Aliquant

Refresh Local InteliSense Cache = Ctrl+Shift+R; Ctrl+R = show/hide results pane (or at least these are my defaults for SSMS2008 and SSMS2014) – Countermark

myfriend told me to block the tablename and press alt + F1 but that shortcut not works on me. this command help me!! thankss – Desk

Answer 3

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')

Lanai

Short and elegant, plus works for me! Only thing is the name value returned is an internal name (methinks), and not the actual column name in the parent table. Any way to fix this? – Imperfect

What I see here that the ParentTableName would always be same as the given 'Tablename' in the where clause (if included). This may be intentional, and will be more useful when queried for more than one table. – Accidence

Answer 4

I found this answer quite simple and did the trick for what I needed: https://mcmap.net/q/53249/-how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server

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://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql

Surcharge

There are five other parameters to filter on, the most useful of which to know is the second where you can specify a non-default schema, e.g. EXEC sp_fkeys 'Payroll', 'accounting' – Highness

This one helped, thx! – Pikestaff

Answer 5

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)

Xenophanes

This solution exec sp_helpconstraint 'Table Name' is the only one that returns any rows at all for me. However the contraint name is gibberish. PRIMARY KEY (clustered) PK__org_soft__3213E83FE6B07364 – Vair

This does only work if you have enough rights to access the table Information Schema – Compile

Answer 6

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

Oratorio

I was looking for a way to see the columns that are foreign keys and the related tables that the column references, and this sums it up nicely. Thanks! – Pean

This was missing some foreign keys on some of my tables while @Aldana answer showed them – Woodbine

Answer 7

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

Hydrolysis

Answer 8

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;

Edging

Answer 9

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.

Miun

Answer 10

In Object Explorer, expand the table, and expand the Keys:

Derte

Answer 11

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'

Carnivore

Answer 12

--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

Sephira

Answer 13

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/

Tereasaterebene

Answer 14

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

Tereasaterebene

Answer 15

The procedure

_sp_help 'tbl_name'_ 

does give a lot of information but I find the procedures

_sp_fkeys 'tbl_name'_ and 
_sp_pkeys 'tbl_name'_ 

easier to use, and maybe with a more future-proof result.

(And they do answer the OP perfectly)

Vernavernacular

Answer 16

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(<main table name>);

this will give the constraint_name, column_names which will be referring and tables which will be depending on the constraint will be there.

Staffard

Answer 17

Littlesweetsea had what I needed, but often it's good to know the schema.

SELECT schema_NAME(f.schema_id) schemaname,
   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) = 'soldiers'

Sesquialtera