Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
T-SQL help to find which tables uses Primary key column values

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

  • Tuesday, February 26, 2013 12:42 AM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Tuesday, February 26, 2013 12:52 AM
     
     Answered Has Code
    Sign In to Vote
    0

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