Search a String Inside a Database

May be you need to know where a string shown by an application you don't know much about has been stored in the related database.

Reverse engeneering purposes, reporting, optimization or simply curiosity.

The following query generates a search script.

SELECT 'USE [' + db_name() + '] --' cmd, -2 rn

UNION ALL

SELECT 'DECLARE @search VARCHAR(100) --' cmd, -1 rn

UNION ALL

SELECT 'SET @search = ''%search this%'' --' cmd, 0 rn

UNION ALL

SELECT

    'IF EXISTS(SELECT TOP 1 1 FROM [' + SCHEMA_NAME(T.schema_id) + '].[' + T.name + '] WITH(NOLOCK) WHERE ' +

    CASE

        WHEN C.collation_name IS NOT NULL THEN '[' + C.name + ']'

        WHEN CT.name IN ('image') THEN 'CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), [' + C.name + ']))'

        ELSE 'CONVERT(VARCHAR(MAX), [' + C.name + '])'

    END +

    ' LIKE @search) PRINT ''[' + SCHEMA_NAME(T.schema_id) + '].[' + T.name + '].[' + C.name + ']'' --' cmd,

    ROW_NUMBER() OVER (ORDER BY SCHEMA_NAME(T.schema_id), T.name, C.name) rn

FROM

    sys.tables T

        INNER JOIN

    sys.columns C ON

        C.object_id = T.object_id

        INNER JOIN

    sys.types CT ON

        CT.user_type_id = C.user_type_id

WHERE

    NOT EXISTS (SELECT TOP 1 1 FROM sys.extended_properties EP WHERE EP.major_id = T.object_id AND EP.name = 'microsoft_database_tools_support') AND

    T.is_ms_shipped = 0 AND

    (C.collation_name IS NOT NULL OR CT.name IN ('image','varbinary','binary','xml')) AND

    C.is_computed = 0

ORDER BY

    2

Run it on the database you want to scan, copy and paste the result in a new window then set the search variable (eventually remove one or both % characters).

USE [MyDatabase] --

DECLARE @search VARCHAR(100) --

SET @search = 'Best%' --

IF EXISTS(SELECT TOP 1 1 FROM [dbo].[Accounts] WITH(NOLOCK) WHERE [AccountName] LIKE @search) PRINT '[dbo].[Accounts].[AccountName]'

...

The results will be printed on the output window.

On large databases you'd better run only a group of search queries then proceed with the next one.

SearchString.zip