sql server - Looking for everywhere a particular value has been used in foreign keys in one pass -

good afternoon.

i using sql server 2008/tsql. important note not have write access db (i read user). not have write access db can insert temp tables if absolutely needed.

i preface letting know have no formal education in sql. makes sense - may inaccurate in vocabulary etc.

scope of trying do: 1. select specific recordid (value) in column (primary key) table 2. find specific number/recordid used in dependents/foreign keys 3. return tablename , columnname count of how many times value found

so, example... have table information on person tied recordid, like: dbo.memberinfo recordid, name etc.

the id number of member (memberinfo.recordid) used in other tables, say: dbo.awards [honoreeid] (dbo.awards.honoreeid=memberinfo.recordid) dbo.address [memberid] (dbo.address.memberid=memberinfo.recordid) dbo.contact [personid] (dbo.contact.personid=memberinfo.recordid) ...and potentially few hundred others

i want run through tables , see how many times particular value/record in use. now, add complexity this, needs generic, column may looking dependents on may change day day. (ex. may looking dependents of eventid tomorrow)

my current process is: -use select find id of person need -look @ foreign keys linked recordid (primary key) of dbo.members -dump tablenames , columns of foreign keys out excel -do find , replace make bunch of select counts where=@variable -put sql, define variable , set equal initial id number

there has better way. have attempted many variations of following lots of errors , no success:

--declare @selected char select t.name, c.name --set @selected=(select t.name sys.tables t) --case when (t.name not null) 1 --else '0' end 'mytrial'  --sys.tables t sys.foreign_key_columns fk inner join sys.tables t on fk.parent_object_id = t.object_id inner join sys.columns c on fk.parent_object_id = c.object_id , fk.parent_column_id = c.column_id referenced_object_id=--insert object id here 

my line of thinking was/is: 1. query foreign keys used in table/column, return table name , column name dependent tables 2. feed these results can build me new query return count of value in each of tables/columns applicable 3. return tablename well, can fed select statement should need @ details making count.

so results might this: tablename, columnname, count of value in column

ideally, no value, table name etc. returned if count less one.

my process may extremely flawed out of gate, offered helps me learn. thanks!

the script below uses no stored procedures use temporary table. far can conceive, there's no way around it. runs me, although working relatively small test database.

this first venture dynamic sql, can't testify safety of code against sql injection attacks , such. i, too, self-taught.

declare @primary_table varchar(100) = ''; declare @column_name varchar(100) = ''; declare @specific_value int = ;  if(object_id('tempdb..#selected_tables') not null) begin     drop table #selected_tables; end  select distinct t.name table_name     , c.name column_name     , cast(null bigint) referenced_records  #selected_tables  sys.foreign_key_columns fk     inner join sys.tables t          on fk.parent_object_id = t.object_id     inner join sys.columns c          on fk.parent_object_id = c.object_id              , fk.parent_column_id = c.column_id     inner join sys.tables t2          on fk.referenced_object_id = t2.object_id     inner join sys.columns c2          on fk.referenced_column_id = c2.column_id  t2.name = @primary_table     , c2.name = @column_name;  declare @sqlcommand nvarchar(max); declare @unprocessed_records int = (select count(1)                                      #selected_tables                                      referenced_records null); declare @processing_table varchar(1000) = (select top 1 table_name                                             #selected_tables                                             referenced_records null); declare @processing_column varchar(1000) = (select top 1 column_name                                             #selected_tables                                             referenced_records null                                                 , table_name = @processing_table);  while @unprocessed_records > 0 begin     set @sqlcommand = 'update #selected_tables '                         + 'set referenced_records = (select count(1) '                         + 'from ' + @processing_table + ' '                         + 'where ' + @processing_column + ' = ' + cast(@specific_value nvarchar(1000)) + ') '                         + 'where table_name = ''' + @processing_table + ''' '                         + 'and column_name = ''' + @processing_column + ''';'      exec (@sqlcommand);      set @unprocessed_records = (select count(1)                                      #selected_tables                                      referenced_records null);     set @processing_table = (select top 1 table_name                                             #selected_tables                                             referenced_records null);     set @processing_column = (select top 1 column_name                                             #selected_tables                                             referenced_records null                                                 , table_name = @processing_table); end;  select * #selected_tables;