Searching All Tables in a SQL Server Database for a value.

One of my IT guys was trying to write a SQL query to search for a keyword in every table and column of a particular database. There are tons of solutions out there on how to do this and from looking at what he had done so far, I decided to see how quickly I could figure out one. Ended up being about 15 minutes.

CAUTION: Probably not the fastest or most optimized solution, but fairly simple:


USE My_Database;

-- Store results in a local temp table so that. I'm using a
-- local temp table so that I can access it in SP_EXECUTESQL.
CREATE TABLE #tmp (
tbl nvarchar(max),
col nvarchar(max),
val nvarchar(max)
);

DECLARE @tbl nvarchar(max);
DECLARE @col nvarchar(max);
DECLARE @q nvarchar(max);
DECLARE @search nvarchar(max) = 'my search key';

-- Create a cursor on all columns in the database
declare c cursor for
SELECT tbls.TABLE_NAME, cols.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES AS tbls
JOIN INFORMATION_SCHEMA.COLUMNS AS cols
ON tbls.TABLE_NAME = cols.TABLE_NAME

-- For each table and column pair, see if the search value exists.
OPEN c
FETCH NEXT FROM c INTO @tbl, @col
WHILE @@FETCH_STATUS = 0
BEGIN
-- Look for the search key in current table column and if found add it to the results.
SET @q = 'INSERT INTO #tmp SELECT ''' + @tbl + ''', ''' + @col + ''', ' + @col + ' FROM ' + @tbl + ' WHERE ' + @col + ' LIKE ''%' + @search + '%'''
EXEC SP_EXECUTESQL @q
FETCH NEXT FROM C INTO @tbl, @col
END
CLOSE c
DEALLOCATE c

-- Get results
SELECT tbl, col, val FROM #tmp

-- Remove local temp table.
DROP TABLE #tmp