Searching in All the Tables - SQL Server Forums

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)

Searching in All the Tables

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-06-27 : 07:22:04

Hi

Is there a way to search for a keyword in ALL the Columns of ALL the Tables ?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 07:23:30

The obvious answer has to be FULL TEXT SEARCH.

If you prefer Dynamic SQL, it would be doable too.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 07:47:48

A good start with Dynamic SQL is


SELECT		so.name TableName,
		sc.name ColumnName,
		st.name TypeName
FROM		syscolumns sc
INNER JOIN	sysobjects so on so.id = sc.id
INNER JOIN	systypes st on st.xtype = sc.xtype
WHERE		so.xtype = 'U'
		and st.name <> 'sysname'
ORDER BY	so.name,
		sc.name

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-27 : 08:39:03

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

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-27 : 12:30:33

I have had this script for some time now. Forget where i got it.


select 
	so.name as [Object Name]
from 
	sysobjects so
inner join 
      syscomments sc 
on 
	so.id = sc.id 
      
WHERE 
	sc.text like '%@@IDENTITY%'
--and so.xtype = 'P' --Search just stored procedures

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources