How to search for all fields like "string" in a database?

[CInman]

I want to search a whole database for fields "like" a string, and cannot work out how to do it? I suspect the stored procedures sp_columns and sp_fields could come into play with some flow control, but I simply cannot get my head round it? Does anyone have any ideas?

 

[JamesLean]

So you just want to find any column names which match your criteria (you don't want to search the actual table contents)? If so, use the INFORMATION_SCHEMA views:

SELECT *
FROM INFORMATION_SCHEMA.columns
WHERE column_name LIKE '<string>'

--James

 

[osjohnm]

I'm a bit unclear of what you are after but if you are looking for actual column names which contain the word string then this will help.

select so.name, sc.name
from sysobjects so, syscolumns sc
where so.id = sc.id
and sc.name like '%string%'

This will return the table name along with any columns that contain the word string in their names.

Does that help you or are you trying to return fields which contain the word "string" as part of the record?

John

 

[CInman]

Brilliant --- I'll try both ideas right now, thanks ;-)