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

    Question

  • 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 t.name AS table_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name AS column_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    WHERE c.name LIKE '%PersonID%'
    ORDER BY schema_name, table_name;

    now

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

    Thanks

    Tuesday, February 26, 2013 12:42 AM

Answers

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







    • Edited by Stan210 Tuesday, February 26, 2013 1:53 AM
    • Proposed as answer by Dineshkumar Tuesday, February 26, 2013 2:03 AM
    • Marked as answer by Kalman TothModerator Monday, March 04, 2013 4:11 PM
    Tuesday, February 26, 2013 12:52 AM