SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

While writing article based on my SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key I got idea about writing this article. I was thinking if you can find primary key for any table in database you can sure find foreign keys for any table in database as well.

In SQL Server 2005 How to Find Tables With Foreign Key Constraint in Database?

Script to find all the primary key constraint in database:
USE AdventureWorks;
GO
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

In SQL Server 2005 How to Find Tables With Primary Key Constraint in Database?
SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

51 thoughts on “SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

  1. Hi Catherine,

    There are different way to query system data. Information_schema and catalog views are two major method.

    As per Microsoft:
    “Catalog views return information that is used by the Microsoft SQL Server 2005 Database Engine. We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.”
    http://msdn2.microsoft.com/en-us/library/ms174365.aspx

    “Some changes have been made to the information schema views that break backward compatibility. These changes are described in the topics for the specific views.”
    http://msdn2.microsoft.com/en-us/library/ms186778.aspx

    Looking at both of them, I have decided to go for catalog views. There are few additional advantages are there to use catalog views.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  2. Pingback: SQL SERVER - Query to display Foreign Key relationships and name of the constraint for each table in Database Journey to SQL Authority with Pinal Dave

  3. /*** Script all Foreign Key Constraints ***/
    /*** The Result Set can be used to copy constraints to your testing DB or to keep on hand in case of errors. ***/
    SELECT
    ‘ALTER TABLE ‘+FK.TABLE_NAME+
    ‘ ADD CONSTRAINT ‘+C.CONSTRAINT_NAME+’ FOREIGN KEY’+
    ‘(‘+CU.COLUMN_NAME+’) ‘+
    ‘REFERENCES ‘+PK.TABLE_NAME+
    ‘(‘+PT.COLUMN_NAME+’)’ ForeignKeyScripts
    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
    –WHERE PK.TABLE_NAME IN (‘Table1′, ‘Table2′)
    –WHERE FK.TABLE_NAME IN (‘Table1′, ‘Table2′)

    • Hi Pinal Sir
      Suppose Order Table has primary key
      & Order table is connected to OrderDetail, Employee (relation one to many)
      So from Order Table I want to identify thaat with which table it has associations (one to many relations) ?

      the query should give answer OrderDetail, Employee

  4. Hi Pinal,
    I have a requirement where in for a given View, I need to find out all the tables that are used in the view and I also need to find out how each table in the view is linked.

    For example, if I create a simple view like

    SELECT *
    FROM TableA
    INNER JOIN TableB ON TableA.ID = TableB.FKID
    INNER JOIN TableC ON TableA.ID = TableC.FKID

    I need to find out all the Tables involved in the view i.e. TableA, TableB and TableC. I also need to find out how TableA and TableB, TableA and TableC are linked (i mean the join Condition).

    Could you please point me to resources that can help me find the answer to this.
    Thanks
    Prasad

  5. @jigar:

    SELECT sfk.fkeyid, sof.name AS fTableName, sor.name AS rTableName, scf.name AS fColName, scr.name AS rColName
    FROM dbo.sysforeignkeys sfk INNER JOIN
    dbo.sysobjects sof ON sfk.fkeyid = sof.id INNER JOIN
    dbo.sysobjects sor ON sfk.rkeyid = sor.id INNER JOIN
    dbo.syscolumns scf ON sfk.fkey = scf.colid AND sof.id = scf.id INNER JOIN
    dbo.syscolumns scr ON sfk.rkey = scr.colid AND sor.id = scr.id

  6. I have written a script which I believe will list all the Foreign Key constraints that are missing supporting indexes. But since I’m very inexperienced with SQL Server I was hoping I could throw this over the fence and let the big dogs chew on it.

    Thanks in advance for any and all criticisim.

    George

    select C.Table_Name,
    C.Constraint_Name,
    C.Constraint_Columns
    from
    (select object_name(i.object_id) table_name,
    i.name index_name,
    max(case index_column_id when 1 then col_name(ic.object_id,ic.column_id) else ” end)+
    max(case index_column_id when 2 then col_name(ic.object_id,ic.column_id) else ” end)+
    max(case index_column_id when 3 then col_name(ic.object_id,ic.column_id) else ” end)+
    max(case index_column_id when 4 then col_name(ic.object_id,ic.column_id) else ” end)+
    max(case index_column_id when 5 then col_name(ic.object_id,ic.column_id) else ” end)+
    max(case index_column_id when 6 then col_name(ic.object_id,ic.column_id) else ” end)+
    max(case index_column_id when 7 then col_name(ic.object_id,ic.column_id) else ” end)+
    max(case index_column_id when 8 then col_name(ic.object_id,ic.column_id) else ” end)+
    max(case index_column_id when 9 then col_name(ic.object_id,ic.column_id) else ” end)+
    max(case index_column_id when 10 then col_name(ic.object_id,ic.column_id) else ” end) index_columns
    from sys.index_columns ic,
    sys.indexes i
    WHERE ic.index_id = i.index_id
    AND ic.object_id = i.object_id
    AND OBJECTPROPERTY(i.OBJECT_ID,’IsUserTable’) = 1
    AND i.index_id != 1
    GROUP BY i.object_id, i.name) as I RIGHT OUTER JOIN
    (select kcu.table_name,
    kcu.constraint_name,
    max(case kcu.ordinal_position when 1 then kcu.column_name else ” end)+
    max(case kcu.ordinal_position when 2 then kcu.column_name else ” end)+
    max(case kcu.ordinal_position when 3 then kcu.column_name else ” end)+
    max(case kcu.ordinal_position when 4 then kcu.column_name else ” end)+
    max(case kcu.ordinal_position when 5 then kcu.column_name else ” end)+
    max(case kcu.ordinal_position when 6 then kcu.column_name else ” end)+
    max(case kcu.ordinal_position when 7 then kcu.column_name else ” end)+
    max(case kcu.ordinal_position when 8 then kcu.column_name else ” end)+
    max(case kcu.ordinal_position when 9 then kcu.column_name else ” end)+
    max(case kcu.ordinal_position when 10 then kcu.column_name else ” end) constraint_columns
    from information_schema.key_column_usage kcu,
    information_schema.referential_constraints rc
    where rc.constraint_name = kcu.constraint_name
    group by kcu.table_name, kcu.constraint_name) as C
    on C.Table_Name = I.Table_Name
    and I.Index_Columns like C.Constraint_Columns + ‘%’
    where I.Table_Name is null

  7. Hi Pinal,

    Your articles are always great source of information. Can u provide code i mean query to add FK on new table

    Thanks
    Tom

    • @TOM i m writting query to create table with fk.

      CREATE TABLE ORDERS
      (Order_ID integer primary key,
      Order_Date datetime,
      Customer_SID integer references CUSTOMER(SID),
      Amount double);

  8. Hi Pinal

    Very interesting article and of great help.
    I made a little addition to your code. As I wanted also to know what the FKs are doing in the Table (referential integrity on update and on delete) I added two columns to your superb query. And a little bit of ordering :)
    Maybe it helps other readers.

    SELECT f.name AS ForeignKey,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,
    fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id,
    fc.referenced_column_id) AS ReferenceColumnName,
    f.update_referential_action_desc AS UpdateAction,
    f.delete_referential_action_desc AS DeleteAction
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc
    ON f.OBJECT_ID = fc.constraint_object_id
    ORDER BY TableName ASC, ColumnName ASC

  9. Pingback: SQL SERVER - 2005 - Find Tables With Foreign Key Constraint in Database - Part 2 Journey to SQL Authority with Pinal Dave

  10. George,

    I’ve a script that does the same but is a little less complicated I think:

    SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
    INNER JOIN sys.foreign_keys f
    ON CCU.TABLE_NAME = object_name(parent_object_id) and CONSTRAINT_NAME = f.name
    WHERE f.type = ‘F’
    AND NOT EXISTS (
    SELECT OBJECT_NAME(i.object_id) AS tablename,
    i.name AS indexname,
    a.name AS columnname
    FROM sys.indexes i
    INNER JOIN sys.index_columns c
    ON i.object_id = c.object_id AND i.index_id = c.index_id
    INNER JOIN sys.all_columns a
    ON a.object_id = c.object_id AND a.column_id = c.column_id
    WHERE CCU.TABLE_NAME = OBJECT_NAME(i.object_id)
    AND CCU.COLUMN_NAME = a.name
    )

    ORDER BY CCU.TABLE_SCHEMA, CCU.TABLE_NAME, CCU.COLUMN_NAME

  11. Hi, ur articles always found very useful. thanks. I hv one query.. i hv constraint on my master table, but there are excess data in my master, which not yet used in sub tables. I want to delete master data which is not used in subtable. Is it possible to delete thru sql? i m using sql 2005.

    thanks

  12. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  13. Pingback: SQL SERVER - 2008 - Find Relationship of Foreign Key and Primary Key using T-SQL - Find Tables With Foreign Key Constraint in Database Journey to SQL Authority with Pinal Dave

  14. Pingback: SQL SERVER - 2005 Find Table without Clustered Index - Find Table with no Primary Key Journey to SQL Authority with Pinal Dave

  15. Excellent query. I was thrown into the project to work on somebody else’s database design. I was supposed to right a data export procedure and this script CAME SO HANDY so find all the keys and the to right the stored procedure that return the data i want. Iit would have been impossible to find the relationships and then develop the procedure. AWESOME AWESOME!!!!!!!!!!!!!!

  16. Thank you for these codes but i need your help seriously

    The codes only display the column names but the foreign keys ar not displayed.

    is there something i need to turn on?

  17. @TOM i m writting query to create table with fk.

    CREATE TABLE ORDERS
    (Order_ID integer primary key,
    Order_Date datetime,
    Customer_SID integer references CUSTOMER(SID),
    Amount double);

  18. i need small help

    i had created different tables in my date base suppose let i have 5 tables in my data base with using primary key and foreign key now i have toknow in this five table which is my main table.?

    what query i have to write for this one.

  19. I have got one more query while doing Google. This query also gives me the foreign key for my database but I am getting different number of foreign key record from this query and from your query which is given above. My query is given below.

    SELECT
    FK.TABLE_NAME AS TableName,
    CU.COLUMN_NAME AS ForeignKey,
    PK.TABLE_NAME AS ReferenceTableName,
    PT.COLUMN_NAME AS ReferenceColumnName,
    C.CONSTRAINT_NAME AS 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

    Please let me know which should I use.

  20. Baratam: When designing or working with a relational database, it is very good practice to create an entity relationship diagram (ERD) for your database. This would make it clear which are parent and which are child tables. If your database had only five tables, this could be done on one sheet of paper.
    —————-
    Jan: In:
    AND NOT EXISTS (
    SELECT OBJECT_NAME(i.object_id) AS tablename,
    i.name AS indexname,
    a.name AS columnname
    FROM sys.indexes i
    …. other joins
    …..

    — you only need
    SELECT 1
    , however when building that subquery, you might need to build a test version using all those columns (and since it is a correlated subquery, modify the test version to contain an extra INNER JOIN).

  21. i need query to find child( foreign key tables) and child’s child tables ..
    simply i want to implement recursive cte table in above query

    i found out below query but i cant stop looping

    ;with child_list1(parent_table, child_table)
    as
    (
    select o1.name as parent_table,o2.name as child_table
    from sys.all_objects o1
    join sys.foreign_key_columns f on o1.object_id = f.referenced_object_id
    join sys.all_objects o2 on o2.object_id = f.parent_object_id
    where o1.name = ‘work_order’ –= (select name from sys.all_objects where object_id = 711673583)
    and o1.name o2.name
    union all

    select o1.name as parent_table,o2.name as child_table
    from sys.all_objects o1
    join sys.foreign_key_columns a on o1.object_id = a.referenced_object_id
    join sys.all_objects o2 on o2.object_id = a.parent_object_id
    inner join child_list1 cl on cl.child_table = o1.name
    and o1.name cl.parent_table
    and o1.name o2.name
    and cl.parent_table o2.name

    )

    select * from child_list1

  22. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #001 « SQL Server Journey with SQL Authority

  23. I’m getting the following SQL Exception Error:

    System.Web.Services.Protocols.SoapException: Server was unable to process request. —> System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_T_WEB_SUB_LINEITEM_TR_PRICE_TYPE”. The conflict occurred in database “Impresario”, table “dbo.TR_PRICE_TYPE”, column ‘id’. Error inserting into t_web_sub_lineitem The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Tessitura.WebAPI.Cart.AddPerformance(SeatRequest oSeatRequest) at Tessitura.WebAPI.Cart.Add(SeatRequest oSeatRequest) at Tessitura.WebAPI.Tessitura.ProcessSeatRequest(SeatRequest seatRequest) at Tessitura.WebAPI.Tessitura.ReserveTicketsDirect(String sWebSessionID, String sPriceType, Int32 iPerformanceNumber, Int32 iNumberOfSeats, Int32 iZone) at Tessitura.WebAPI.Tessitura.ReserveTickets(String sWebSessionID, String sPriceType, Int32 iPerformanceNumber, Int32 iNumberOfSeats, Int32 iZone) — End of inner exception stack trace —

  24. Hi pinal
    I have table which is referenced buy some other tables and those referenced tables also reference to some other table
    now i want to delete entry from main table where i have not set on delete cascade with all foreign keys so if i will take your query in cursor and delete the entries in references table then error comes becoz we did not set tables order
    so can u help

  25. hi
    i had a problem..
    suppose a primary table had a foreign key with Secondary table.how can we know the data present in primary table is present secondary table or not?

  26. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s