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

Updating All SQL Table Rows with a Fallback Default Value

I’m working on merging two systems together and that involves mapping some of the database content in system B to system A. Basically I have a table from system B that has an identifier column that will not map to system A, so I’ve added a new column (new_system_a_id) to that table:

System B Table
—————
nvarchar | old_identifier
int | new_system_a_id

Now I needed a way to map all of system A id’s to System B Table. I created a mapping table with the following:

Mapping Table
—————
nvarchar | name
int | system_a_id

From here I just ran


update system_b_table
set new_system_a_id = (select system_a_id from mapping_table where name = old_identifier)

This worked fine, except that every row in system b’s table that did not have a mapping was set to NULL. To change it to a default value I just used COALESCE function:


/* Update system_b_table with the new mapping. If there is no mapping, default to 555. */
update system_b_table
set new_system_a_id = coalesce(
(select system_a_id from mapping_table where name = old_identifier),
555
)