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

SQL SERVER - 2005 - Find Tables With Foreign Key Constraint in Database keyicon While writing articles based on my Find Table without Clustered Index – Find Table with no Primary Key I got an idea about writing this article. I was thinking if you can find primary key for any table in the database, you can sure find foreign key for any table in the database as well. If you have database and it is not forcing a database relationship, I suggest you look at your design once again. Relational database without keys and connection, may not make sense at all.

The keys are very, very critical to any database system as they are the one who enforces the system

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?
Find Tables With Primary Key Constraint in Database

Please leave a comment right below.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text
Next Post
SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

Related Posts

48 Comments. Leave new

  • 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.”
    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/catalog-views-transact-sql?view=sql-server-2017

    “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.”
    https://docs.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/system-information-schema-views-transact-sql?view=sql-server-2017

    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 ( https://blog.sqlauthority.com/ )

    Reply
  • I’m curious why you chose this path instead of utilizing information_schema? What’s the benefit, the down-side?

    Reply
  • MichaelAaronGriffey
    September 21, 2007 12:05 pm

    /*** 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’)

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

      Reply
  • How to write a SQL script to find those tables in which foreign key constraints are missing.

    Reply
  • hello,

    I like have script that lists out all the fk contsraints in sql server 200.

    Thanks In ADV,

    Jack

    Reply
  • Hi

    @Milli,
    This can be only done manually analyzing the data.

    @Jigar,
    I only support SQL Server 2005.

    Regards,
    Pinal Dave ( https://blog.sqlauthority.com/ )

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

    Reply
  • @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

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

    Reply
  • Hi Pinal,

    Your articles are always great source of information.

    Kind Regards,

    Azim

    Reply
  • sanjoy Banerjee
    May 5, 2008 7:31 pm

    i want to know the Foreign key name between two tables.

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

    Reply
    • @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);

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

    Reply
  • simply superb! gr8 work!

    Reply
  • Jan Vandenpanhuyzen
    September 11, 2008 4:57 pm

    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

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

    Reply
  • It’s really very good script.it is very unique and useful script.keep it up pinal.You are doing very well job.

    Reply
  • Dude that rocks! You saved me a lot of frustation. Thank you. Thank you.

    Reply
  • 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!!!!!!!!!!!!!!

    Reply
  • Hello,

    How can i get unique key constains of table1.

    Reply

Leave a Reply

Menu