T-SQL help to find which tables uses Primary key column values


  • Hi Experts , i have  a table tblperson  

    Create table tblperson ( Personid int pk, FN varchar(10)  , LN varchar(10), Address varchar(10)  ) ---- this is my parent table and there are 5 other tables which uses personid  as child( foreign keys )

    like tblstate , tblcontact and  etc ....

    i have a querey which gives all tables which uses Personid meaning tblstate has personid, tblcontact has personid

    SELECT AS table_name,
    SCHEMA_NAME(schema_id) AS schema_name, AS column_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    WHERE LIKE '%PersonID%'
    ORDER BY schema_name, table_name;


    I want to give pass a Person Id ( int ) for example 10 ,  to the script and have it tell me if the Person Id is used in any of the tables.

    declare  @person id INT = 10

    T_SQL to show which table uses person id column and has value 10

    can any one please help me on writing a script for this Please Please ...


    Tuesday, February 26, 2013 12:42 AM


  • in simple terms,

    select Top 1 'Table1' from Table1 where personid=10 union select Top 1 'Table2' from Table2 where personid=10 union select Top 1 'Table3' from Table3 where personid=10 union select Top 1 'Table4' from Table4 where personid=10 union select Top 1 'Table5' from Table5 where personid=10

    your query is about meta data..but you want to query real data..

    I am not sure if there is direct way without querying individual tables..

    Tuesday, February 26, 2013 12:52 AM