-2

I've been scouring online for an example of how to do this but haven't found anything at all. All the queries I've found assume you know what table you want to search.

I'm looking for a SQL query to simply search the ENTIRE database for a specific word.

There has to be such a thing right?

This is for MS SQL 2005/2008

Thanks

vnat
  • 55
  • 1
  • 1
  • 8

1 Answers1

0

What do you mean by "entire database"? You need to find your values in tables only, or in object definitions, too? I assume the former. In this case, you don't have to really know the structure of your DB. Try those views below. With them, you can construct your select queries on all the tables / colums. Just filter out non-*char columns, views and system tables, and you're ready to go - you can "automatically" generate multiple select statements.

select top 100 * from information_schema.tables
select top 100 * from information_schema.columns

The other option, is to use some addon to SSMS, like this one: http://www.ssmstoolspack.com/ It has an option to search entire database.

But be advised, that both solutions will have a great impact on performance of your server.

AdamL
  • 12,421
  • 5
  • 50
  • 74
  • Thanks very much, I also found this related solution that I tried yesterday that works very well: http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database – vnat May 01 '13 at 14:25