views:

71

answers:

3

Is there a way I can search for a particular string value in a SQL SERVER 2005 database? I have this image path value that I want to modify and I think it's stored in the database.

Also - is there a way that I can search for a column's name for any table in a database? Likewise, search for a tables name in a database?

+1  A: 

Use this to get a list of all of the tables in a database.

SELECT *
FROM information_schema.Tables

Ust this to get a list of all of the columns in a table

SELECT column_name 'Column Name'
FROM information_schema.columns
WHERE table_name = MyTableName

You can use these together to search through every table and column to search for your string.

Irwin M. Fletcher
A: 

There may exist utilities which drill down the catalog/schema of a given server/database and use this info to search for a given match (or partial match) with a given pattern in all the text-like columns. You could possibly even write such a utility and introduce heuristics of your own (say not to search tables which have more than 10,000 rows because you expect the info to be a "configuration-type" table etc.)

An easier way may be to use SQL-Profiler to record every query sent to server (possibly filtered by IP address, by session id etc..). By then running the underlying application so that it would produce the particular string, you'll have a [hopefully] short list of queries, one of which targets the particular table / column where the path/string or parts therefore gets queried from.

mjv
A: 

Sometimes folks do this with dynamic sql to search across every column in the database. It won't perform all that well, but for a one-off search like this can be quite handy. A quick google search turned up this example - http://codesnippets.joyent.com/posts/show/2157 This is a pretty poor example since it queries each table N times where N is the number of columns (it should just do one table scan instead of N), but it might get the job done. You may also want to not limit to the first 100 chars like that example does.