FIND PARTICULAR VALUE ANYWHERE IN DATABASE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

FIND PARTICULAR VALUE ANYWHERE IN DATABASE

Author

Topic

doco
Yak Posting Veteran

77 Posts

Posted - 2008-02-21 : 22:15:04

Forgive the title. Didn't know how else to do it and make it brief.

I know how to find all the tables in a database that have a particular field name


SELECT
    so.name as table_name
FROM
    sysobjects so inner join syscolumns sc
    on so.id = sc.id
WHERE
    sc.name like 'property%'

What I would like to do is search the entire database for a particular field value and return the table_name.field_name. Is this possible?

Education is what you have after you've forgotten everything you learned in school

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-21 : 22:49:33

Following code will generate sql statements for you:

declare @searchstring varchar(100)
set @searchstring ='%sys%'
Select 'Select "'+object_name(id)+'" as tablename,count(*) as NoofRowsMatch from '+object_name(id) + ' where '+name +' like "'+@searchstring +'"'
from syscolumns where xtype in (175,239,99,231,35,167)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-22 : 01:28:23

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources