SQL SERVER – Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database

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

This is very long query. Optionally, we can limit the query to return results for one or more than one table.

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='something'WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')

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

SQL Constraint and Keys, SQL Scripts, SQL Stored Procedure
Next Post
SQL SERVER – Query to Find ByteSize of All the Tables in Database

Related Posts

136 Comments. Leave new

  • Dakshina Murthy
    April 27, 2009 3:50 pm

    Hi,

    It was great, it helped me in finding the duplicate FK for the same set of tables for the same set of fields.

    With Regards
    Dakshina Murthy

    Reply
  • Hi
    How to retrieve all columns of the given table and its foreign key table.
    Regards
    Jaseem, A.

    Reply
  • This script is amazing, it saved my life.

    Reply
  • You guys rock! This has saved me sooo much time. Thanks all

    Reply
  • Good script !!! saved lot time.

    Reply
  • Rajnish Awasthi
    October 1, 2009 11:12 am

    Very nice script, it is really helpful.

    Reply
  • once again your SQL scripts have proven to be incredibly useful.
    Thank you!

    Reply
  • Excellent script
    than kyou
    it hepls me a lot.
    I have question
    i have several(around 70) tables.
    Person table is the primary table.personId is the primary key in this table. eventhough primary key is unique I have several duplicate rows in the primary table(like all fields are the same except personID[primarykey]).
    I want to delete duplicates from the primary key table and same time I want to update that foreign key with the other in the foreign key table.
    could u help please help me regarding this.
    thank u

    Reply
  • Hello Usha,

    There is no inbuilt functionality to perform the complete task straightway. You will have to write a batch to complete it. Here I can provide you a guideline to implement.
    Suppose the design of your Person table is as following:

    PersonID Name Age
    1 ABC 30
    2 XYZ 35
    3 ABC 30

    Here you want that in all foreigh key tables update the PersonID 3 (duplicate record) to 1 and then from Person table delete the record where PersonID is 3.

    At first Get the minimum PersonID for all duplicate records using following script:

    WITH CTE (PersonIDOld, PersonIDNew, DuplicateCount)
    AS
    (
    SELECT PersonID as PersonIDOld, MIN (PersonID) OVER(PARTITION BY Name, Age) AS PersonIDNew,
    ROW_NUMBER() OVER(PARTITION BY Name, Age ORDER BY Name, Age) AS DuplicateCount
    FROM Person
    )

    SELECT PersonIDOld, PersonIDNew INTO #temp
    FROM CTE
    WHERE DuplicateCount > 1

    Note: Here replace the column Name, Age with all columns of Person table except Primary Key column.

    Now update foreign key of PersonID in all foreign key tables as following:

    UPDATE FKTable1
    SET PersonID = PersonIDNew
    FROM FKTable1 INNER JOIN #temp tmp
    ON FKTable1.PersonID = tmp.PersonIDOld

    At last delete all duplicate records from Person table as following:

    DELETE Person
    FROM Person INNER JOIN #temp tmp
    ON Person.PersonID = tmp.PersonIDOld

    Please let me know if you need more clarification or if you have any issue.

    Kind Regards,
    Pinal Dave

    Reply
  • thank you so much sir
    I think it will workIi am beginner to sql
    I don’t know how to create and run batch
    could you help me reagrdng this

    SELECT PersonIDOld, PersonIDNew INTO #temp
    FROM CTE
    WHERE DuplicateCount > 1

    I executed until this as a query it shows 392 rows affected.

    I try to update the foreginkey query
    but it is not recognizing
    PersonIDNew,
    PersonIDOld
    could u help me regarding this.
    ————————————————
    and I have one more question
    can I update all the foreign key tables at a time and delete the duplicates from Primary key table.
    Can I run all this asa one batch
    —————————————————
    I have foreign key tables iin different databases also
    I am totally using 3 databases
    I need to update the foreign key’s from all those tables based on this primary key
    ————————————————————————
    and one condition i need to check
    in some tables this foreign key is also used as a primary key
    at that time it shows a error message like
    sequence contains more than one element
    when I try to oupdate that foreign key with new value
    —————————————————
    actually I wrote queries and try to access those through C#.net
    If u provide any direct solution through sql server or through C# programming is good for me.
    ———————————————–
    It is my first job I am working on this from last 3 weeks.
    If u provide any solution to this, I am really thank ful to you
    I appreciate it

    Reply
  • could any one reply to my question,plese?
    i am relly waiting for the suggestions.

    Reply
  • I trie the CTE query
    it
    IT is working fine
    thank u som much
    but now I had another probem like
    when i try to update foreign key tables
    some tables are updating
    but some tables are shows a message like

    Msg 2627, Level 14, State 1, Procedure T_UPD_Membership_Question, Line 14
    Violation of PRIMARY KEY constraint ‘PK_Membership_Question’. Cannot insert duplicate key in object ‘dbo.Membership_Question’.
    The statement has been terminated.

    even I tried by removing relaton ships.still it won’t allow me

    please help me regrding this.

    Reply
  • Hello Usha,

    The error “Violation of PRIMARY KEY constraint ‘PK_Membership_Question’. Cannot insert duplicate key in object ‘dbo.Membership_Question’.
    ” occures when duplicate vaues are inserted in Primary key column.

    Make sure that foreign-key column is not primary-key or a part of primary-key in foreign-key table. If that is the case than you will have to drop the Primary-key constraint from foreign-key column in foreign-key table.

    Kind Regards,
    Pinal Dave

    Reply
  • thank u so much Mr. Dave
    I did try that
    for some tables it is working fine
    but for some tables it repetedly displays same message.
    is there any thing like even after i deleting the primary key and disabling triggers and unique indexes.

    can i check the entire row before updating
    if it isalready there then deleete the row and insert the new one which I wnat to update.
    or any other suggestions regarding this.
    thnk you

    Reply
  • Sir i have a Problem statement given below plz canu help me of making query of this type of statement:

    Using the data dictionary of Oracle server, write down a single SQL statement to retrieve the parent and child relationship between all the tables of the currently connected schema.

    Sample output from HR schema:

    Employees ———hasParent———>Departments

    Departments ——-hasParent———>Locations

    Locations———hasParent———>Countries

    Countries———hasParent———>Regions

    Reply
  • Hi

    folloing query is running sucessfully second one is not running

    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

    regards

    Jayant

    Reply
  • Wow great it helped me a lot….
    Thankx

    Reply
  • Fantastic Blog… I always find it very useful..

    I have a question , hopefully you can help. I’d like to include views in this script. I’m querying Microsofts CRM system and can only use views, it’s been heavily customised and looks pretty confusing.

    What I’d like to see is the view name and column name where the column within the view is a key column. For example If we have an Account table with a primary key of AccountID and there are another 5 tables with a foreign key relationship back to the account table. I’d like to see any views referencing these 5 tables that contain the foreign key column in the view defintion… Hope that’s clear. I’d be very grateful if you could offer some help..

    Regards

    Paul

    Reply
  • Carlos Porras (El Salvador)
    February 23, 2010 2:58 am

    Hi Mr. Pinal Dave,

    Is it possible to combine your query (the one shown above) with this one as to get a single file at once.

    What I’d like to do is to build the [(DisplayName …… )] of the second part of the query with the results of the query you have provided here as to build a single file.

    select vrstica from
    ( SELECT t.name as tabela, as sort, ‘[MetadataType(typeof(‘ + t.name + ‘Metadata))]’
    + ‘ public partial class ‘ + substring(t.name, 1, len(t.name)-1) + ‘ {[DisplayName(“‘ + t.name +'”)]’
    + ‘ public class ‘ + t.[name] + ‘Metadata{‘ as vrstica FROM sysobjects t WHERE t.xtype=’U’
    union all
    SELECT sysobjects.name, 2, ‘ [DisplayName(“‘ + replace(syscolumns.name,’ ‘,’_’) + ‘”)]’
    + case syscolumns.isnullable when 0 then ‘ [Required()]’ else ” end
    + ‘ public object ‘ + replace(syscolumns.name,’ ‘,’_’) + ‘{get;set;}’
    + ‘ //’ + systypes.name
    + ‘(‘ + ltrim(rtrim(str(isnull(syscolumns.prec,0)))) + ‘,’ + ltrim(rtrim(str(isnull(syscolumns.scale,0)))) + ‘)’
    + ‘ ‘ + ltrim(rtrim(str(syscolumns.length)))
    FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype = systypes.xtype and systypes.name not like ‘sysname’
    WHERE sysobjects.xtype=’U’
    union all
    SELECT t.[name], 3, ‘}}’
    FROM sysobjects t
    WHERE t.xtype=’U’
    ) t
    ORDER BY tabela, sort

    Thanks in advance

    Carlos Porras (El Salvador)

    Reply
  • Hi!
    Use Pinal’s beautiful script to list all PK and FK keys in a DB/table

    SELECT DISTINCT
    KeyColName = PT.COLUMN_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
    –Optional – to show only one/limited list of tables
    –WHERE i1.TABLE_NAME IN (‘object_registry’)
    ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
    ORDER BY PT.COLUMN_NAME

    BR
    /witecat

    Reply

Leave a Reply