November 6th, 2013, 11:32 PM
How to search the whole database tables in all fields for a specific value
Hi to all,
I'm not that good in database yet but I want to learn, but I'm currently facing a problem and I'm bounded by deadline, I'm currently looking for a certain table in a database that contains a specific value but the problem is that there are more than 100 tables in the database also every table there are more than 10-30 fields which makes it very troublesome to do an individual search or select statement per table per field thus I would like to ask for anyone who could help me if you know how to create a global select or search statement/ procedure or anything that could search the whole database tables regardless of any field were the value would reside. thanks.
Here is the sql statement to retreive all database tablefields
select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
November 19th, 2013, 08:56 AM
I suggest you run the output of this query then search for the text in the output – hopefully your database is not too big.
select 'select '''+(name)+''', * from '+name from sys.objects where type='U'