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 (http://blog.SQLAuthority.com)

About these ads

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

  1. Hi
    Thanks for your query, it set me off in the right direction :).
    Much Appreciated.

    I have rewritten the query to be slightly faster:

    SELECT
    CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
    TABLE_CATALOG = FK.TABLE_CATALOG,
    TABLE_SCHEMA = FK.TABLE_SCHEMA,
    TABLE_NAME = FK.TABLE_NAME,
    COLUMN_NAME = FK_COLS.COLUMN_NAME,
    REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
    REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
    REFERENCED_TABLE_NAME = PK.TABLE_NAME,
    REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
    AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
    AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME

    Hope this helps :)
    -Mark

    • The linq version is:
      var foreignKeys =
      from rc in datacontext.REFERENTIAL_CONSTRAINTs
      join tc in datacontext.TABLE_CONSTRAINTs
      on new { rc.CONSTRAINT_CATALOG, rc.CONSTRAINT_SCHEMA, rc.CONSTRAINT_NAME }
      equals new { tc.CONSTRAINT_CATALOG, tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME }
      where tc.CONSTRAINT_TYPE == “FOREIGN KEY”
      join tc2 in datacontext.TABLE_CONSTRAINTs
      //on rc.UNIQUE_CONSTRAINT_CATALOG equals tc2.CONSTRAINT_CATALOG
      on new { cat = rc.UNIQUE_CONSTRAINT_CATALOG, sch = rc.UNIQUE_CONSTRAINT_SCHEMA, nam = rc.UNIQUE_CONSTRAINT_NAME }
      equals new { cat = tc2.CONSTRAINT_CATALOG, sch = tc2.CONSTRAINT_SCHEMA, nam = tc2.CONSTRAINT_NAME }
      where tc2.CONSTRAINT_TYPE == “PRIMARY KEY”
      join kcu in datacontext.KEY_COLUMN_USAGEs
      on rc.CONSTRAINT_NAME equals kcu.CONSTRAINT_NAME
      join kcu2 in datacontext.KEY_COLUMN_USAGEs
      on rc.CONSTRAINT_NAME equals kcu2.CONSTRAINT_NAME

      select new
      {
      CONSTRAINT_NAME = rc.CONSTRAINT_NAME,
      TABLE_CATALOG = tc.TABLE_CATALOG,
      TABLE_SCHEMA = tc.TABLE_SCHEMA,
      TABLE_NAME = tc.TABLE_NAME,
      COLUMN_NAME = kcu.COLUMN_NAME,
      REFERENCED_TABLE_CATALOG = tc2.TABLE_CATALOG,
      REFERENCED_TABLE_SCHEMA = tc2.TABLE_SCHEMA,
      REFERENCED_TABLE_NAME = tc2.TABLE_NAME,
      REFERENCED_COLUMN_NAME = kcu2.COLUMN_NAME
      };

      • slight error, this should be the correct one:
        var foreignKeys =
        from REF_CONST in datacontext.REFERENTIAL_CONSTRAINTs
        join FK in datacontext.TABLE_CONSTRAINTs
        on new { REF_CONST.CONSTRAINT_CATALOG, REF_CONST.CONSTRAINT_SCHEMA, REF_CONST.CONSTRAINT_NAME }
        equals new { FK.CONSTRAINT_CATALOG, FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME }
        where FK.CONSTRAINT_TYPE == “FOREIGN KEY”
        join FK2 in datacontext.TABLE_CONSTRAINTs
        on new { cat = REF_CONST.UNIQUE_CONSTRAINT_CATALOG, sch = REF_CONST.UNIQUE_CONSTRAINT_SCHEMA, nam = REF_CONST.UNIQUE_CONSTRAINT_NAME }
        equals new { cat = FK2.CONSTRAINT_CATALOG, sch = FK2.CONSTRAINT_SCHEMA, nam = FK2.CONSTRAINT_NAME }
        where FK2.CONSTRAINT_TYPE == “PRIMARY KEY”
        join FK_COLS in datacontext.KEY_COLUMN_USAGEs
        on REF_CONST.CONSTRAINT_NAME equals FK_COLS.CONSTRAINT_NAME
        join PK_COLS in datacontext.KEY_COLUMN_USAGEs
        on FK2.CONSTRAINT_NAME equals PK_COLS.CONSTRAINT_NAME

        select new
        {
        CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
        TABLE_CATALOG = FK.TABLE_CATALOG,
        TABLE_SCHEMA = FK.TABLE_SCHEMA,
        TABLE_NAME = FK.TABLE_NAME,
        COLUMN_NAME = FK_COLS.COLUMN_NAME,
        REFERENCED_TABLE_CATALOG = FK2.TABLE_CATALOG,
        REFERENCED_TABLE_SCHEMA = FK2.TABLE_SCHEMA,
        REFERENCED_TABLE_NAME = FK2.TABLE_NAME,
        REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
        };

  2. Mark,

    Thanks for sharing the information. It is important to learn and share. I am glad that you follow the same understanding.

    Very good script.

    Kind Regards,
    Pinal

    • Hi Pinal,

      Is there any other query to be written to show the hierarchial relationships between the tables exists in a DB. The DB diagram would show the logical representation but most of the times we dont have the prvileges to view.

      So far i have worked in Oracle, it has the level pseudo column and a hierarchial query joining with dictionary table would give you all the details. In this we can filter with the table names. So that it would be easier to analyse the tables relation as a module wise.

      Can you help me in this?

      Regards
      Mohamed

    • Hi Pinal,

      Thanks for the query. Similar kind of queries i had written but with sys.foreign_keys views instead of REFERENTIAL_CONSTRAINTS.

      Is there any other query to be written to show the hierarchial relationships between the tables exists in a DB. The DB diagram would show the logical representation but most of the times we dont have the prvileges to view.

      So far i have worked in Oracle, it has the level pseudo column and a hierarchial query joining with dictionary table would give you all the details. In this we can filter with the table names. So that it would be easier to analyse the tables relation as a module wise.

      Can you help me in this?

      Regards
      Mohamed

  3. Hi Pinal,

    Am a beginner in SQLServer and I find your script to be very helpful.

    Do you have a similar script that lists all types of constraints, i.e. check constraints etc.?

    Any pointers to the right direction will be very much appreciated.

  4. Hey, this is really a good querry, We can use when we want to know that how many primary keys and forign keys are there in the database.

    BUt as Subhash said that he used this querry to “find duplicate relationships” in the database.
    Wht is mean by “duplicate relationships” ???

  5. Hi,

    What is the query used to display all tables names in sql server (Query analyzer) ?

    Pls reply to this question? ASAP

    thanks and regards
    n.m.

  6. Hi there again

    I was just re-looking at this script, and was wondering about something:

    For the line in the script above that reads:
    WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’

    Is this necessary, because don’t all foreign key constraints correspond to Primary Keys? In the REFERENTIAL_CONSTRAINTS table, the FK columns correspond to UNIQUE_CONSTRAINT…. columns. Can a FK correspond to any unique constraint or only a Primary Key constraint?

    Thanks

  7. Sorry, just to clarify the above comment:
    The above mentioned where clause in your SQL is deftinately necessary because of the nested select statement.
    Removing the CONSTRAINT_TYPE clause in my script above will not make it any faster.
    I am just wondering what applies conceptually :)

  8. This is not a comment rather question.

    I went through the above post and found very interesting. I tried to modify it according to my problem but no luck.

    Problem.

    how to get the list of tables who has the FK relation ship with the given tables. once i get the list I want to delete the record from the parent table and set the references to null from the table where parents pk is referred as fk.

    I am not using cascade delete since I don’t want to delete the complete row.

    right now i am doing this by writing the simple SP and manually setting the null to the referenced tables.

    Thanks you very much in advance.

    -Santosh Maskar

  9. hi
    I need to delete the paticular records from parent table without affecting the child table(will have more child table) and also the relationship between the records should be removed,

    since am going to use the unaffected record in child table as parent table..
    Hope u got it..
    Your help is appreciated.

    Thanks & Regards
    Bala

  10. This was a great tutorial! It was no problem at all to adapt this and create a routine that automatically fixed that pesky name mangling the occurs in Sql Server constraints ( so that FK__Trader__departme__4B7734FF becomes FK__Trader__department_id).

    You helped me solve a real headache.

  11. This is good but if I have multiple columns in my foreign keys it will multiply the results so that 2 column returns 4 rows of data and 3->9 and so on.

    Adding the following line to Marks solution will reduce the amount of lines
    AND FK_COLS.ORDINAL_POSITION = PK_COLS.ORDINAL_POSITION

    Otherwise this was really helpful.

  12. Hi all,

    Great script, really helpful. I was wondering, is there a way i can change this script so that I can get a sensible order of integrity?

    Basically my problem is that we have this ‘sync’ service that packages data up from remote sites and sends it to our central database. At the moment it just pipes all the data in an manual order that we set because we know about the constraints. What i would like is to be able to know which tables need to have their data first (ie tables that have their primary keys referenced as foreign keys in other tables).

    Is there a way to do that?

  13. I needed a script which find all the table where primary key of a table is used in other tables as foreign key. i need to delete a record in master table ..
    This script worked well for me.
    thanks

  14. suppose i have number of select statements in a single sp in sqlserver 2005.
    i need to know how many statements are there in that sp.

  15. hi…

    i want to delete rows from tables having “UserID” Coulmn in a databse named “Test” . but some tables are foreignkey constraints… how it can be done…

    first i have to find all tables having UserID column. then delete the rows from tables containg foreign key constraint(i.e where UserID=’SOMETHING’)…after that delete the tables having primary key..in a database…

    SUMMARY:: I HAVE TO CLEAR ALL THE ROWS FROM ALL THE TABLES HAVING USERID COLUMN IN A DATABASE WHERE SOME ARE FOREGNKEY CONSTRAINTS AND SOME ARE PRIMARY KEY…

    HOW IT’S POSSIBLE…

  16. PINAL, I have a somewhat related problem that i was wondering if you had a solution for

    There are 4 tables with relationships to each other. ConferenceRoom, ConferenceRoomCapacity, TeleTrackPhone, and RPSLocation

    The table ConferenceRoom stores attributes pertaining to conference rooms.

    The table ConferenceRoomCapacity stores attributes pertaining to the maximum capacity of the conference room.

    The table TeleTrackPhone contains different phone numbers. It stores ConferenceRoomID and RPSLocationID as FKs.

    The table RPSLocation stoes info about different locations, city, state, etc.

    The TeleTrackPhone table allows duplicate values for the ConferenceRoomID because a conference room can have more than one phone number.

    This is the query that I run.

    SELECT DISTINCT A.ConferenceRoomID, A.ConferenceRoomName, C.City, C.State
    FROM ConferenceRoom As A, TeleTrackPhone As B, RPSLocation As C, ConferenceRoomCapacity As D
    WHERE A.ConferenceRoomID = B.ConferenceRoomID
    AND B.RPSLocationID = C.RPSLocationID
    AND A.ConferenceRoomID = D.ConferenceRoomID

    My problem is that it returns duplicates if the conference room has multiple phone numbers and i only want the query to return a list of conference rooms.

  17. Hello,

    This one is simpler, just gives you table_name and which type of key information and key name.

    select OBJECT_NAME(PARENT_OBJ) TABLE_NAME, CASE WHEN XTYPE =’F’ THEN ‘FORIEGN KEY’ ELSE ‘PRIMARY KEY’ END KEY_TYPE , NAME KEY_NAME
    from sysobjects where Xtype in (‘F’ , ‘pK’) ORDER BY XTYPE DESC

    I am using only sysobjects table to get this information.

    Hope this helps.
    Imran.

  18. Hi,
    here’s another version (mine :-; ) to extract foreign keys.

    Replace ‘TABLE_NAME’ with yours … !

    Regards
    Karim Laurent

    select OBJECT_NAME(FKEYS.PARENT_OBJECT_ID) source ,PKCOLUMN_NAME= convert(sysname,col1.name), OBJECT_NAME(FKEYS.referenced_object_id) destination, FKCOLUMN_NAME = convert(sysname,COL2.name)

    from

    sys.columns COL1,

    sys.columns COL2,

    sys.foreign_keys FKEYS

    inner join sys.foreign_key_columns KEY_COLUMN on (KEY_COLUMN.constraint_object_id = FKEYS.object_id)

    where

    COL1.object_id = FKEYS.referenced_object_id

    AND COL2.object_id = FKEYS.parent_object_id

    AND COL1.column_id = KEY_COLUMN.referenced_column_id

    AND COL2.column_id = KEY_COLUMN.parent_column_id

    AND OBJECT_NAME(FKEYS.PARENT_OBJECT_ID)=’TABLE_NAME’

  19. MAKE IT MORE SHORT N SIMPLE
    Will work on both 2000 and 2005
    ===========================

    select object_name(constid) FKey_Name, object_name(fkeyid) Child_Table, c1.name FKey_Col,
    object_name(rkeyid) Parent_Table, c2.name Ref_KeyCol
    from sysforeignkeys s
    inner join syscolumns c1
    on ( s.fkeyid = c1.id
    and s.fkey = c1.colid )
    inner join syscolumns c2
    on ( s.rkeyid = c2.id
    and s.rkey = c2.colid )

  20. I want to retrieve only limited data from database like limit is keyword in mysql which retrieve limited data according to our arguments. Like I want to load only 10 records from the database at first display in page. but I don’t want to using top. Because its create problem of paging. So please give me alternate solution for that’d have use row index but in that row index I have to write inner query and my data base I s to heavy.

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

  22. Excellent site.

    The queries above (except for Smeet’s) return extra incorrect rows for compound FKs.

    I ran a SQL Profiler trace while scripting out a compound FK in Management Studio and here is what I found in the Profiler trace. The result additionally outputs an ID column that displays the correct order in which the PK table columns are referenced.

    Cheers

    select tbl.name as [Table_Name] ,
    cstr.name as [ForeignKey_Name],
    fk.constraint_column_id as [ID] ,
    cfk.name as [Name] ,
    crk.name as [ReferencedColumn]
    from sys.tables as tbl
    inner join sys.foreign_keys as cstr
    on cstr.parent_object_id=tbl.object_id
    inner join sys.foreign_key_columns as fk
    on fk.constraint_object_id=cstr.object_id
    inner join sys.columns as cfk
    on fk.parent_column_id = cfk.column_id
    and fk.parent_object_id = cfk.object_id
    inner join sys.columns as crk
    on fk.referenced_column_id = crk.column_id
    and fk.referenced_object_id = crk.object_id
    order by [Table_Name] asc ,
    [ForeignKey_Name] asc,
    [ID] asc

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

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

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

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

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

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

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

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

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

  32. 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)

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

  34. Hi, I have 2 tables (Staff and Family Mem) with a one to many relationship..

    In Family Mem table, one family member can belong to only one staff, while staff can have more than one family mem.

    In staff table, staffid is the PK while staffid is the FK in the family table.

    I would like to know the query/way to relate these two tables using foreign key if I am using XAMPP?

    And how should I write the INSERT statement to insert into both tables at once since the staff’s registration form also contain data that belongs to the family mem table…

    PLS help… Thxxxx so much…

  35. Hi
    I find your scripts really helpful!

    i developed this shorter one from some of your codes:

    SELECT K.TABLE_NAME AS TableName, K.COLUMN_NAME AS ColumnName, OBJECT_NAME(S.referenced_object_id) AS RefTableName,
    COL_NAME(S.referenced_object_id, S.referenced_column_id) AS RefColumn, K.CONSTRAINT_NAME AS ConstraintName
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K INNER JOIN
    sys.foreign_key_columns AS S ON K.TABLE_NAME = OBJECT_NAME(S.parent_object_id)
    WHERE (K.TABLE_NAME = @TableName)
    ORDER BY ConstraintName DESC

    I want the PK and all FKs in the table. I get it but it returns them in duplicate lines. Can you help me narrow to single line results?

    thanks

  36. @M
    Great script! I believe this is the only one mentioned here that returns the correct results for fks referencing non-primary, unique-key columns. Also it works with linked servers so I can look for missing fks on production by comparing to development.
    Thanks!

  37. Hi Pinal, this is a seriously good script and it must feel great that you have helped so many people over such a long period of time.

    Thanks!

  38. Hello,

    the above script has a bug. When an FK consists of two columns, it produces a cross-product of four rows for that FK, not two rows.

    I still need to analyze the query to understand and fix it.

    Greetings, Christoph

  39. Pinal,
    There are a couple of corrections needed to your query; it does not handle FK’s linked through unique constraints, and it does not handle multi-column keys well. Also, there was no need for the in-line ‘pt’ table. Here is he revised query:
    SELECT
    FK_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
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcu
    ON rc.UNIQUE_CONSTRAINT_NAME = pkcu.CONSTRAINT_NAME
    WHERE
    fkcu.ORDINAL_POSITION = pkcu.ORDINAL_POSITION

    Even with all the above changes, the query will not be able to handle FK relationships linked to an independantly created unique index (i.e. index created using ‘create index’ statement).

  40. Pinal ,

    how do i find the relation ship between attributes in a table ,

    i need to write a query which runs on a table

    (table name will be passed as the parameter to the query )

    and tel me the relationship between the primary key column and the other columns in the same table , result should tell me the relationship is whether its 1:1 or 1:m or M:1 or M:M taking primary key as the granular level

    Regards,

    Ashok

  41. for the scripts below, where do i put my table name and colum names

    select object_name(constid) FKey_Name, object_name(fkeyid) Child_Table, c1.name FKey_Col,
    object_name(rkeyid) Parent_Table, c2.name Ref_KeyCol
    from sysforeignkeys s
    inner join syscolumns c1
    on ( s.fkeyid = c1.id
    and s.fkey = c1.colid )
    inner join syscolumns c2
    on ( s.rkeyid = c2.id
    and s.rkey = c2.colid )

  42. Edit: Added constraint type

    select tbl.constraint_name
    , tbl.constraint_type
    , tbl.table_schema
    , tbl.table_name
    , col.column_name
    , col3.table_schema as ref_table_name
    , col3.table_name as ref_table_name
    , col3.column_name as ref_column_name
    from information_schema.table_constraints tbl with(nolock)
    left join information_schema.constraint_column_usage col with(nolock) on tbl.constraint_name = col.constraint_name
    left join information_schema.referential_constraints col2 with(nolock) on tbl.constraint_name = col2.constraint_name
    left join information_schema.constraint_column_usage col3 with(nolock) on col2.unique_constraint_name = col3.constraint_name
    order by tbl.table_name

  43. hi guys….

    create table emp
    (e_id int primary key,
    e_name varchar(255))

    insert table emp values(1,’aa’);
    insert table emp values(2,’bb’)

    then i created new table:

    create table dept
    (d_id int primary key,
    d_name varchar(255),
    e_id int foreign key references emp(e_id))

    with this st. it works well:
    insert table dept values(11,’computer’,1);

    this st. creats problem:
    insert table dept values(33,’marketing’,3);

    so plz help on this problem

  44. error for the above problem:

    Msg 547, Level 16, State 0, Line 1
    The INSERT statement conflicted with the FOREIGN KEY constraint “FK__t22__e_id__4FD1D5C8″. The conflict occurred in database “master”, table “dbo.t11″, column ‘e_id’.
    The statement has been terminated.

      • hi
        madhivanan,
        i know that ,but my question is i have created one table with primary column but when i create another table with foreign key of references to first table primary column. when i insert data it does not except it and give above error….

        plz let me know were i m going wrong
        thanks in advances

  45. hi,

    I want to display the table which does not have foreign key.
    Actually i have a db with 143 tables. Now i want to know the table which does not have any foreign key relationship as i have perform delete query on those tables.
    Awaiting your response desperately.
    Thank you

  46. Hi,

    i got the answer, i think this might help you.

    — To display table does not having foreign key—

    SELECT SCHEMA_NAME(t.schema_id) AS schema_name
    ,t.name AS table_name
    FROM sys.tables t
    WHERE object_id NOT IN
    (
    SELECT parent_object_id
    FROM sys.foreign_keys
    WHERE type_desc = ‘Foreign_KEY_CONSTRAINT’ — or type = ‘PK’
    –where type = ‘FK’
    );

  47. this script is correct ?

    use xyz

    /*

    Truncate All tables within a database

    */

    Set NoCount ON

    Declare @tableName varchar(200)
    Declare @tableName1 varchar(200)

    set @tableName=”

    DECLARE @intFlag INT
    Declare @count INT

    –Find all child tables and those which have no relations

    select ROW_NUMBER() over (order by t.table_name) sno, T.table_name
    into #childtables
    from INFORMATION_SCHEMA.TABLES T
    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    on T.table_name=TC.table_name where (TC.constraint_Type =’Foreign Key’
    or TC.constraint_Type is NULL) and
    T.table_name not in (‘dtproperties’,’sysconstraints’,’syssegments’)
    and Table_type=’BASE TABLE’ and T.table_name > @TableName

    –Find all Parent tables

    select ROW_NUMBER() over (order by t.table_name) sno,T.table_name
    into #parenttables
    from INFORMATION_SCHEMA.TABLES T
    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    on T.table_name=TC.table_name where TC.constraint_Type =’Primary Key’
    and T.table_name ‘dtproperties’and Table_type=’BASE TABLE’
    and T.table_name > @TableName

    –select * from #childtables
    –select * from #parenttables

    SET @intFlag = 1
    SET @count = ( Select COUNT(*)as countValue from #childtables )

    – Truncate All Child tables
    WHILE (@intFlag <= @count)
    BEGIN
    Select @tableName1 = table_name from #childtables where sno = @intFlag
    SET @intFlag = @intFlag + 1
    Print @tableName1
    Exec('Truncate table '+@tableName1)
    print @tableName1 + ' truncated successfully '
    END

    SET @intFlag = 1
    SET @count = ( Select COUNT(*)as countValue from #parenttables)

    – Truncate All Parent tables
    WHILE (@intFlag <= @count)
    BEGIN
    Select @tableName1 = table_name from #parenttables where sno = @intFlag
    SET @intFlag = @intFlag + 1
    Print @tableName1
    Exec('Truncate table '+@tableName1)
    print @tableName1 + ' truncated successfully '

    END

    Set NoCount Off

  48. Optimized Code :

    SELECT b.TABLE_NAME,d.COLUMN_NAME,c.Table_name,e.column_name,a.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a INNER JOIN
    INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE b
    ON a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE c ON
    a.UNIQUE_CONSTRAINT_NAME=c.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE d ON
    d.CONSTRAINT_NAME=a.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE e ON
    a.UNIQUE_CONSTRAINT_NAME=e.CONSTRAINT_NAME

  49. Hi,
    Please suggest me simple query.
    if two tables are there like:
    create table sample(s_id int primary key,name varchar(20));
    create table customer(s_id int foreign key references sample,remark varchar(10));
    Now if i have to store data of ‘ABC’ having s_id=101,name=’ABC’,remark=’Good’.
    then how will be the insert query for this?

    ???

  50. Thanks so much for the script. Seems to get muddled on multi part foreign keys. I have added a few lines to match PK to FK on data type and max length which works perfectly for my purposes

    SELECT K_Table = FK.TABLE_NAME ,
    fk.TABLE_SCHEMA ,
    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,
    i1.TABLE_SCHEMA
    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
    INNER JOIN INFORMATION_SCHEMA.COLUMNS FKC ON fk.TABLE_NAME = fkc.TABLE_NAME AND fk.TABLE_SCHEMA = fkc.TABLE_SCHEMA AND cu.COLUMN_NAME = fkc.COLUMN_NAME
    INNER JOIN INFORMATION_SCHEMA.COLUMNS PKC ON PT.TABLE_NAME = pkc.TABLE_NAME AND PT.TABLE_SCHEMA = pkc.TABLE_SCHEMA AND pt.COLUMN_NAME = pkc.COLUMN_NAME
    WHERE fk.TABLE_SCHEMA = ‘dbo’
    AND fkc.DATA_TYPE = PKC.DATA_TYPE AND fkc.CHARACTER_MAXIMUM_LENGTH = PKC.CHARACTER_MAXIMUM_LENGTH
    ORDER BY 1, 6

  51. Heya i am for the primary time here. I found this board and I in finding It really helpful & it helped me out much. I’m hoping to present something back and aid others such as you helped me.

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

    • @ Yogish,

      Index Seeks are good comparing to Index Scans.

      In Index Seeks, SQL Server query optimizer uses Indexes and as a result queries perform better. In Index Scans, SQL Server query optimizer uses indexes but ends up scanning all records of the index, this is called as Index scan. Index scans are bad.

      There is tons and tons of material online on this topic.

  53. Hi Pinal,

    Please correct me if i am wrong.

    Below query is also returning same result. Why do we need extra joins with
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS VIEW.

    select
    pk.TABLE_NAME ‘PK_Table’
    , pk.COLUMN_NAME ‘PK_Column’
    , fk.TABLE_NAME ‘FK_Table’
    , fk.COLUMN_NAME ‘FK_Column’
    , c.CONSTRAINT_NAME
    from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK on FK.CONSTRAINT_NAME = c.CONSTRAINT_NAME
    inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK on PK.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME
    ORDER BY
    1,2,3,4

  54. hello pinal sir
    i knew that seven type of backup in sql server 2005
    i don’t what is use of seven type of backup and what is a query of seven type of backup and also possible of work in sql server express edition 2005
    plz sir help me about this topic

  55. Dear sir and all friends

    i have on request i knew sql server view, stored procedure and triggers. but i want to become sql administrator. plz help me about this topic log shipping, sql tuning,ssis package, mirror, 7 types of backup,cluster. i want to do practice of admin part. if any website than i want to learn or plz help me about this topic

    advance thanks for pinal and madhivanan sir

  56. THIS QUERY IS NOT CORRECT FOR RELATIONSHIPS WITH MORE THAN ONE FIELD!
    FK_COLUMN is correct but it shows the first field for PK_COLUMN in all rows.

  57. You know, I just spent several hours trying to fix this so it would display multiple field indexes correctly with no luck. I could sure use it if anyone can figure out how it is done.

  58. GOT IT! (I think) Mark’s solution along with merix’s addition seems to handle all cases well. One of the solutions handled multiple fields but for some reason threw out a lot of the constraints. The code below works but I take NO credit for it.

    SELECT
    CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
    TABLE_CATALOG = FK.TABLE_CATALOG,
    TABLE_SCHEMA = FK.TABLE_SCHEMA,
    TABLE_NAME = FK.TABLE_NAME,
    COLUMN_NAME = FK_COLS.COLUMN_NAME,
    REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
    REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
    REFERENCED_TABLE_NAME = PK.TABLE_NAME,
    REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
    AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
    AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME AND FK_COLS.ORDINAL_POSITION = PK_COLS.ORDINAL_POSITION

  59. And, if you want to query off the sys tables rather than information_schema, this might be up your alley. It is narrow in scope (assumes “dbo” for schema, doesn’t list schema, etc), but it can certainly be enhanced to do so. It runs for one table at a time, but it gives both the parent key relationships, as well as other tables that are foreign keys to ‘YourTableName’.

    SELECT Fk.name AS [Constraint],
    object_name(Fk.parent_object_id) AS FK_table,
    col_name(Fk.parent_object_id, Fk_Cl.parent_column_id)
    AS FK_column,
    TbR.name AS PK_table,
    col_name(Fk.referenced_object_id, Fk_Cl.referenced_column_id)
    AS PK_column
    FROM sys.foreign_keys Fk
    LEFT JOIN sys.tables TbR
    ON TbR.object_id = Fk.referenced_object_id
    JOIN sys.foreign_key_columns Fk_Cl
    ON Fk_Cl.constraint_object_id = Fk.object_id
    WHERE Fk.parent_object_id = object_id(‘YourTableName’)
    OR Fk.referenced_object_id = object_id(‘YourTableName’)
    ORDER BY CASE
    WHEN object_name(Fk.parent_object_id) = ‘YourTableName’ THEN ’0′
    ELSE object_name(Fk.parent_object_id)
    END,
    CONVERT(int,
    CASE
    WHEN ISNUMERIC(REPLACE(Fk.name, object_name(Fk.parent_object_id) + ‘FK’, ”)) = 0 THEN ’0′
    ELSE REPLACE(Fk.name, object_name(Fk.parent_object_id) + ‘FK’, ”)
    END),
    CASE
    WHEN object_name(Fk.parent_object_id) = ‘item’ THEN TbR.name
    ELSE object_name(Fk.parent_object_id)
    END

  60. Thanks Dave, very handy, specially when putting together ETL process to populate Data Warehouses (Kimball models).

  61. All of these scripts are wrong.

    You are assuming the foreign key relationships link to a primary key field. It is quite possible that a foreign key relationship link to an index with a unique constraint.

  62. Here is a working script:

    SELECT ForeignKeys.name [ForeignKeyName], PrimaryKeyTable.name [PrimaryTableName], PrimaryKeyColumn.name [PrimaryColumnName],
    ForeignKeyTable.name [ReferenceTableName], ForeignKeyColumn.name [ReferenceColumnName],
    ForeignKeys.update_referential_action_desc [UpdateAction], ForeignKeys.delete_referential_action_desc [DeleteAction]
    FROM sys.foreign_keys ForeignKeys
    JOIN sys.foreign_key_columns ForeignKeyRelationships ON (ForeignKeys.object_id = ForeignKeyRelationships.constraint_object_id)
    JOIN sys.tables ForeignKeyTable ON ForeignKeyRelationships.parent_object_id = ForeignKeyTable.object_id
    JOIN sys.columns ForeignKeyColumn ON (ForeignKeyTable.object_id = ForeignKeyColumn.object_id AND ForeignKeyRelationships.parent_column_id = ForeignKeyColumn.column_id)
    JOIN sys.tables PrimaryKeyTable ON ForeignKeyRelationships.referenced_object_id = PrimaryKeyTable.object_id
    JOIN sys.columns PrimaryKeyColumn ON (PrimaryKeyTable.object_id = PrimaryKeyColumn.object_id AND ForeignKeyRelationships.referenced_column_id = PrimaryKeyColumn.column_id)
    ORDER BY ForeignKeys.name

  63. Hi
    I have a MySQL database that consists of 40 tables and is located on.
    My knowledge of the database that is on a piece of code I am not able to find all Relationships database.
    When using the following code fragment shows an error message .
    Piece of code :

    SELECT
    Table_Name = FK.TABLE_NAME,
    Primary_Key = PT.COLUMN_NAME,
    Foreign_Key = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_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

    Error message :

    #1052 – Column ‘Table_Name’ in field list is ambiguous

    Please use the code please ?
    Thanks

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