none
How to get PrimaryKey and ForignKeys between two tables by using sql query ? RRS feed

  • Question

  • I need to get PrimaryKey and ForignKeys between two tables by using sql query .

    I need to write query or stored procedure or any way without opening tables and see it

    I need to get my result as below :

    PrimaryTable  field ForignTable ForignKey

    Example 

    Trxinvh

    Serial      pk

    Year       pk

    DateTransaction

    Trxinvf

    Serial      fk

    Year       fk

    DateTransaction

    result must be as following

    PrimaryTable  primarykey      ForignTable ForignKey
    
    trxinvh          serial      trxinvf        serial
    
    trxinvh          year       trxinvf         year



    Monday, September 23, 2019 5:09 AM

Answers

  • SELECT 
          
          [child_table] = OBJECT_NAME(f.parent_object_id),
          [child_column] = cc.name,
          [parent_table] = OBJECT_NAME(f.referenced_object_id),
          [parent_column] = pc.name
    FROM
          sys.foreign_keys f
    INNER JOIN
    (
          SELECT 
                c.[object_id],
                c.name,
                c.column_id,
                ic.index_id
          FROM
                sys.columns c
          INNER JOIN
                sys.index_columns ic
          ON 
                c.[object_id] = ic.[object_id]
                AND c.column_id = ic.column_id
    ) AS pc
    ON
          f.key_index_id = pc.index_id
    INNER JOIN
          sys.foreign_key_columns fkc
    ON 
          f.[object_id] = fkc.constraint_object_id
          AND pc.[object_id] = fkc.referenced_object_id
          AND fkc.referenced_column_id = pc.column_id
    INNER JOIN
          sys.columns cc
    ON 
          fkc.parent_object_id = cc.[object_id]
          AND fkc.parent_column_id = cc.column_id
    WHERE OBJECT_NAME(f.parent_object_id)='trxinvf'
    and OBJECT_NAME(f.referenced_object_id)='trxinvh'        

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 23, 2019 5:34 AM
    Answerer

All replies

  • SELECT C.TABLE_CATALOG,
              C.TABLE_SCHEMA,
              C.TABLE_NAME,
              C.COLUMN_NAME,
              R.CONSTRAINT_NAME AS FK,
              R.UNIQUE_CONSTRAINT_NAME AS PK,
              P.TABLE_NAME AS PK_TABLE,
              P.COLUMN_NAME AS PK_COLUMN
    FROM INFORMATION_SCHEMA.COLUMNS AS C
    JOIN INFORMATION_SCHEMA.TABLES AS T
      ON C.TABLE_CATALOG = T.TABLE_CATALOG
     AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
     AND C.TABLE_NAME = T.TABLE_NAME
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS U
      ON C.TABLE_CATALOG = U.TABLE_CATALOG
     AND C.TABLE_SCHEMA = U.TABLE_SCHEMA
     AND C.TABLE_NAME = U.TABLE_NAME
     ANd C.COLUMN_NAME = U.COLUMN_NAME
    LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS R
      ON U.CONSTRAINT_CATALOG = R.CONSTRAINT_CATALOG
     AND U.CONSTRAINT_SCHEMA = R.CONSTRAINT_SCHEMA
     AND U.CONSTRAINT_NAME = R.CONSTRAINT_NAME
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS P
      ON R.UNIQUE_CONSTRAINT_CATALOG = P.CONSTRAINT_CATALOG
     AND R.UNIQUE_CONSTRAINT_SCHEMA = P.CONSTRAINT_SCHEMA
     AND R.UNIQUE_CONSTRAINT_NAME = P.CONSTRAINT_NAME
     AND U.ORDINAL_POSITION = P.ORDINAL_POSITION
    WHERE T.TABLE_TYPE = 'BASE TABLE';

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 23, 2019 5:15 AM
    Answerer
  • thank you for reply

    i need to write what firsttable='' and secondtable=''

    how to write that based on two tables trxinvh and trxinvf

    Monday, September 23, 2019 5:20 AM
  • Hi

    Can you try below query that list's out all PK and FK relationship from a database.

    SELECT o2.name AS Referenced_Table_Name,
           c2.name AS Referenced_Column_As_FK,
           o1.name AS Referencing_Table_Name,
           c1.name AS Referencing_Column_Name,
    s.name AS Constraint_name
    FROM  sysforeignkeys fk
    INNER JOIN sysobjects o1 ON fk.fkeyid = o1.id
    INNER JOIN sysobjects o2 ON fk.rkeyid = o2.id
    INNER JOIN syscolumns c1 ON c1.id = o1.id AND c1.colid = fk.fkey
    INNER JOIN syscolumns c2 ON c2.id = o2.id AND c2.colid = fk.rkey
    INNER JOIN sysobjects s ON fk.constid = s.id
    ORDER BY o2.name

    My output,

    Ref link: https://www.codeproject.com/Tips/1129790/List-Primary-Key-and-Foreign-Key-Relationship-in-D

    Thanks

    ME_77




    • Edited by ME_77 Monday, September 23, 2019 5:23 AM
    Monday, September 23, 2019 5:21 AM
  • SELECT 
          
          [child_table] = OBJECT_NAME(f.parent_object_id),
          [child_column] = cc.name,
          [parent_table] = OBJECT_NAME(f.referenced_object_id),
          [parent_column] = pc.name
    FROM
          sys.foreign_keys f
    INNER JOIN
    (
          SELECT 
                c.[object_id],
                c.name,
                c.column_id,
                ic.index_id
          FROM
                sys.columns c
          INNER JOIN
                sys.index_columns ic
          ON 
                c.[object_id] = ic.[object_id]
                AND c.column_id = ic.column_id
    ) AS pc
    ON
          f.key_index_id = pc.index_id
    INNER JOIN
          sys.foreign_key_columns fkc
    ON 
          f.[object_id] = fkc.constraint_object_id
          AND pc.[object_id] = fkc.referenced_object_id
          AND fkc.referenced_column_id = pc.column_id
    INNER JOIN
          sys.columns cc
    ON 
          fkc.parent_object_id = cc.[object_id]
          AND fkc.parent_column_id = cc.column_id
    WHERE OBJECT_NAME(f.parent_object_id)='trxinvf'
    and OBJECT_NAME(f.referenced_object_id)='trxinvh'        

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 23, 2019 5:34 AM
    Answerer