Search all columns of a table for a value?

Question

I've looked for an answer to this, but all I can find is people asking how to search all columns of ALL tables in a database for a value. I just want to search all columns for a specific table. The code people have come up with for the all tables question is complicated and hard for me to figure out where exactly it's searching a specific table. Can somebody help me out? Thanks

Answer 1

Just use some third party tool. There are several that are 100% free and you can’t go wrong with any of these because they will save you a ton of time.

ApexSQL Search (searches both schema and data), SSMS Toolpack (searches schema and data but not free for SQL Server 2012), SQL Search (searches data only).

Frankly, I don’t really understand why even very experienced DBAs bother writing scripts for this if they can use some tool for free that will do the job.

Answer 2

I have no idea of the column types or data values you're searching for, but I'd guess you're trying to search for a substring among multiple text columns.

This is a job for Full-Text Search.

Don't waste time with LIKE '%' + @SearchStr + '%'. You have to write a lot of complicated code to support it, and that solution won't perform well anyway.

Answer 3

In a similar question I mentioned SQL Workbench/J.

The command that searches the database can also be limited to just one table. So even if that question was PostgreSQL specific, the tool works for SQL Server as well as far as I know.