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;
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 ...
select Top 1 'Table1' from Table1 where personid=10
select Top 1 'Table2' from Table2 where personid=10
select Top 1 'Table3' from Table3 where personid=10
select Top 1 'Table4' from Table4 where personid=10
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 byStan210Tuesday, February 26, 2013 1:53 AM
Proposed as answer byDineshkumarTuesday, February 26, 2013 2:03 AM
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.