find what table a value comes from

[tezzyr]

I have a value (11832) which relates to an accountnumber in an sql server db.
is there any way i can find out from which table this value comes from?
eg select * from * where *=11832?
i need to add a field to a report but ive no clue where the field comes from and there are 400 tables in the db to choose from! none of which are named anything obvious!
thanks for your help!

 

[philhege]

Here's a script that queries all tables in the current DB that have an integer or numeric column for the value you specify. Chugs, but gets the job done. Just scroll through the results to find records that match. Or, you can tweak it to save the results somehow (hey, I'm on my lunch break).

-----BEGIN CODE-----

SET NOCOUNT ON

declare @value varchar(32)
declare @sql varchar(8000)
declare @columnlist varchar(256)
declare @wherelist varchar(1024)
declare @tablename varchar(128)
set @value = '11352' --Any integer value (but represented as a character)
select DISTINCT rtrim(table_name)as table_name, 0 as processed into #tables
FROM INFORMATION_SCHEMA.columns where data_type IN('int','numeric')
AND left(table_name,3) <> 'vw_' --Or however you define view names

while (select count(*) from #tables where processed = 0 ) > 0
BEGIN

set @columnlist = ''
set @wherelist = ''

select top 1 @tablename = table_name from #tables
WHERE processed = 0

--select * from information_schema.columns where left(table_name,4) <> 'web_' and left(table_name,3)='vw_'

--construct where clause
select @wherelist = @wherelist + COALESCE('['+column_name+'] = '+@value+' OR ','')
FROM INFORMATION_SCHEMA.columns WHERE table_name = @tablename
AND data_TYPE IN('int','numeric')
IF len(@wherelist)>0
BEGIN

--print @wherelist
set @wherelist = left(@wherelist,len(@wherelist)-3)
--print @wherelist

set @sql = 'SELECT '''+@tablename+''' as table_name, * FROM [' +@tablename+ '] WHERE ' + @wherelist
print @sql
IF right(@sql,len(@value)) = @value
exec (@sql)
ELSE
PRINT 'String too long to process.'




END

UPDATE #tables set processed = 1 where table_name = @tablename
END


drop table #Tables
SET NOCOUNT OFF


-----END CODE-----

Have fun!

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.