Search for one value in any column of any table inside a database

Question

Is there a way to search for one value (in my case it is a UID of the type char(64)) inside any column of any table inside one MS SQL Server database?

I'm sitting in front of a huge database without any idea how the tables had to be linked together. To find that out I'd like to list all tables and there columns that contain a certain value in any row. Is that possible?

One way could be to just dump the entire database into a text file and than use any text-editor to search for the value - but this would be pure pain if the database is too huge.

Answer 1

How to search all columns of all tables in a database for a keyword?

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

EDIT: Here's the actual T-SQL, in case of link rot:

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM #Results
 END

Answer 2

All third=party tools mentioned below are 100% free.

I’ve used ApexSQL Search with good success for searching both objects and data in tables. It comes with several other features such as relationship diagrams and such…

I was a bit slow on large (40GB TFS Database) databases though…

Apart from this there is also SSMS Tools pack that offers a lot of other features that are quite useful even though these are not directly related to searching text.

Answer 3

I expanded the code, because it's not told me the 'record number', and I must to refind it.

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

-- Copyright @ 2012 Gyula Kulifai. All rights reserved.
-- Extended By: Gyula Kulifai
-- Purpose: To put key values, to exactly determine the position of search
-- Resources: Anatoly Lubarsky
-- Date extension: 19th October 2012 12:24 GMT
-- Tested on: SQL Server 10.0.5500 (SQL Server 2008 SP3)

CREATE TABLE #Results (TableName nvarchar(370), KeyValues nvarchar(3630), ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    ,@TableShortName nvarchar(256)
    ,@TableKeys nvarchar(512)
    ,@SQL nvarchar(3830)

SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''

    -- Scan Tables
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
    Set @TableShortName=PARSENAME(@TableName, 1)
    -- print @TableName + ';' + @TableShortName +'!' -- *** DEBUG LINE ***

        -- LOOK Key Fields, Set Key Columns
        SET @TableKeys=''
        SELECT @TableKeys = @TableKeys + '''' + QUOTENAME([name]) + ': '' + CONVERT(nvarchar(250),' + [name] + ') + ''' + ',' + ''' + '
         FROM syscolumns 
         WHERE [id] IN (
            SELECT [id] 
             FROM sysobjects 
             WHERE [name] = @TableShortName)
           AND colid IN (
            SELECT SIK.colid 
             FROM sysindexkeys SIK 
             JOIN sysobjects SO ON 
                SIK.[id] = SO.[id]  
             WHERE 
                SIK.indid = 1
                AND SO.[name] = @TableShortName)
        If @TableKeys<>''
            SET @TableKeys=SUBSTRING(@TableKeys,1,Len(@TableKeys)-8)
        -- Print @TableName + ';' + @TableKeys + '!' -- *** DEBUG LINE ***

    -- Search in Columns
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        ) -- Set ColumnName

        IF @ColumnName IS NOT NULL
        BEGIN
            SET @SQL='
                SELECT 
                    ''' + @TableName + '''
                    ,'+@TableKeys+'
                    ,''' + @ColumnName + '''
                ,LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            --Print @SQL -- *** DEBUG LINE ***
            INSERT INTO #Results
                Exec (@SQL)
        END -- IF ColumnName
    END -- While Table and Column
END --While Table

SELECT TableName, KeyValues, ColumnName, ColumnValue FROM #Results
END

Answer 4

Source: http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/

I have a solution from a while ago that I kept improving. Also searches within XML columns if told to do so, or searches integer values if providing a integer only string.

/* Reto Egeter, fullparam.wordpress.com */

DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

WHILE @TableName IS NOT NULL
BEGIN
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)
IF @TableName IS NOT NULL
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)
AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)
AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)'
INSERT INTO @ColumnNameTable
EXEC (@sql)
WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
BEGIN
PRINT @ColumnName
SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),'''
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
INSERT INTO #Results
EXEC(@sql)
IF @@ROWCOUNT > 0 IF @FullRowResult = 1
BEGIN
SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
' FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
EXEC(@sql)
END
DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
END 
END
END
SET NOCOUNT OFF

SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType

Answer 5

I found a fairly robust solution at https://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58 , which I thought was worth pointing out. It searches columns of these types: varchar, char, nvarchar, nchar, text. It works great and supports specific table-searching as well as multiple search-terms.

Answer 6

There is a nice script available on http://www.reddyss.com/SQLDownloads.aspx

To be able to use it on any database you can create it like in: http://nickstips.wordpress.com/2010/10/18/sql-making-a-stored-procedure-available-to-all-databases/

Not sure if there is other way.

To use it then use something like this:

use name_of_database

EXEC spUtil_SearchText 'value_searched', 0, 0

Answer 7

After trying @regeter's solution and seeing it didn't solve my problem when I was searching for a foreign/primary key to see all tables/columns where it exists, it didn't work. After reading how it failed for another who tried to use a unique identifier, I have made the modifications and here is the updated result: (works with both int, and guids... you will see how to easily extend)

CREATE PROC [dbo].[SearchAllTables_Like]
(
        @SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN

        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('guid', 'int', 'char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM #Results
END

Answer 8

I needed this functionality recently and nothing out there, either articles or here on Stack Overflow was of much use, everything either didn't work correctly, had errors or was dreadfully slow.

I created the following procedure which can be used to search for text in any column in any table in any SQL Server database.

Note it's a work-in-progress and [as of now] has only existed for a couple of days, I'll update with any bug fixes.

It's reasonably fast, has some customisation options that I needed to help with large databases and does not use any loops / cursors.

It's a System Procedure, meaning you create it in Master and can then use it in the context of any database.

It returns results showing the table(s), the column(s) (and data types) containing the searched for text - and a SQL snippet to quickly cut and paste to find the relevant rows.

Parameter descriptions:

@Find               - Required - 'text to search for, including any wildcards'
@Schema             - Optional - only search tables in specific schema
@IncludeMax         - Optional - Specify to search in Varchar(max) columns, default is to ignore
@IncludeHistory     - Optional - Specify to include searching in system-versioned tables, default is to ignore, assumes schema name is "history"
@IncludeAudit       - Optional - Specify to search CreatedBy / AlteredBy columns, default is to ignore
@MaxTables          - Optional - Limits how many tables to search - default is -1 for no limit
@TnameList          - Optional - Provide a comma-delimited list of terms to match with or ignore tables, prefix with "~" for exclude list
@CnameList          - Optional - Provide a comma-delimited list of terms to match with or ignore columns, prefix with "~" for exclude list
@RowLimit           - Optional - Limits the search to tables with fewer than N rows, default is 100k rows
@Refresh            - Optional - Search results are stored and retrieved per search term/database - use this to re-do the search

Some example usage:

exec sp_FindTextInAnyTable @Find = '%textToFind%'; 
/* Find the search term within text any column in any table using default options  */
exec sp_FindTextInAnyTable @Find = '%myname@mydomain%', @Refresh = 1; /* Repeat a previous search and check all tables again instead of returning cached results */

exec sp_FindTextInAnyTable @Find = '%textToFind%', @IncludeMax = 1;
/* Also search any varchar(max) column */

exec sp_FindTextInAnyTable @Find = '%textToFind%', @RowLimit = -1;
/* Search any table regardless of size */

exec sp_FindTextInAnyTable @Find = '%textToFind%', @TnameList = 'config, setting'
/* Search only in tables with the text "config" or "setting" in the name */

exec sp_FindTextInAnyTable @Find = '%textToFind%', @TnameList = '~notes'
/* Search all tables except any with "notes" in the name */

Here's the procedure definition:

    USE MASTER;
    GO
    
    
create or alter procedure sp_FindTextInAnyTable
@Find nvarchar(100), /* Like predicate - supply wildcards in the value */
@SearchNumbers tinyint = 0, /* If searching for numbers: 0 = look at text type columns only 1 = look at numeric type columns only, 2 = look at all types */
@Schema sysname = null, /* search only tables in a specific schema or NULL for all schemas */
@IncludeMax bit = 0, /* Set to 1 to include varchar(max) columns, by default these are excluded */
@IncludeHistory bit = 0, /* Set to 1 to include history tables, by default these are excluded */
@IncludeAudit bit = 0, /* Set to 1 to include CreatedBy / AlteredBy columns, by default these are excluded */
@MaxTables int = -1, /* Set the maximum number of tables to search, set to -1 for no limit (be careful and only run on a replica in production) */
@TnameList varchar(200) = null, /* Comma-delimited list of words to match with table names to search or NULL to search all tables; prefix the list with ~ to exclude tables instead */
@CnameList varchar(200) = null, /* Comma-delimited list of words to match with column names to search or NULL to search all columns; prefix the list with ~ to exclude columns instead (except audit columns) */
@RowLimit bigint = 100000, /* Default max size of table in rows - prevents hitting large tables unless required, ignored if specific list of tables used */
@Refresh bit = 0, /* Set to 1 to have existing search re-evaluated, otherwise data is returned from last search result */
@Debug bit = 0
as
set nocount, xact_abort on;

/*

Run this in the context of any database to search within every CHAR or VARCHAR column
in every table in the database for the specified text to find.

If the text exists in any row it returns the column and table containing the text and
the specific sql to run to find the text in the table.

example usage
exec sp_FindTextInAnyTable @Find = 'black'; /* Find any table with specific value "black" in any column, excludes any history tables, audit columns and varchar(max) columns, limits to first 50 tables */
exec sp_FindTextInAnyTable @Find = '%spoke%'; /* Find the text "spoke" within text any column in any table  */
exec sp_FindTextInAnyTable @Find = '%sys%', @IncludeAudit = 1; /* Find within any column in any table, include searching within createdby/alteredby columns  */
exec sp_FindTextInAnyTable @Find = '%sys%', @Refresh = 1; /* Repeat a previous search and check all tables again instead of returning cached results */
exec sp_FindTextInAnyTable @Find = '%scot%', @TnameList = 'file, log', @Refresh = 1; /*Find any column containing the text in only in tables with "file" or "log" in its name */
exec sp_FindTextInAnyTable @Find = '%scot%', @TnameList = 'file, log', @IncludeHistory = 1, @Refresh = 1; /* Repeat the above search but also search in History tables */
exec sp_FindTextInAnyTable @Find = '%scot%', @Refresh = 1, @Schema = 'history' /* Only search the History tables */
exec sp_FindTextInAnyTable @Find = '%scot%', @TnameList = '~file, log',@Refresh = 1; /* Search all tables except those with file or log in the name */

*/
declare @sql nvarchar(max), @br varchar(2) = Iif(@Debug = 1, Char(13), ''), @Valid bit = 0, 
    @TExclude bit = (Iif(@TnameList like '~%', 1, 0)), @CExclude bit = (Iif(@CnameList like '~%', 1, 0));
declare @Union varchar(20) = Concat(' union all ', @br);
declare @TNames table(tname sysname);
declare @CNames table(cname sysname);

insert into @TNames(tname) 
select Trim([value]) 
from String_Split(Replace(@TnameList, '~',''), ',');

insert into @CNames(cname) 
select Trim([value]) 
from String_Split(Replace(@CnameList, '~',''), ',');

if Object_Id('master.dbo.sp_FindTextInAnyTableResults') is null
begin;
    create table master.dbo.sp_FindTextInAnyTableResults(
        DBId int not null, 
        Find nvarchar(100) not null, 
        Tname sysname not null, 
        Cname sysname not null, 
        Row_Count bigint not null, 
        Datatype varchar(50), [SQL] varchar(max) not null, 
        CreateDate datetime2(0) not null default(GetDate()), 
        Id int identity
    );
    alter table dbo.sp_FindTextInAnyTableResults add constraint [PK_sp_FindTextInAnyTableResults] primary key clustered (dbid, Find, Id) with (fillfactor = 100) on [PRIMARY];
end;

if @Refresh = 1 or @Debug = 1 or not exists (select * from master.dbo.sp_FindTextInAnyTableResults where DBId = Db_Id() and Find = @Find)
begin
    delete from master.dbo.sp_FindTextInAnyTableResults where DBId = Db_Id() and Find = @Find;

    with TList as (
        select Concat_Ws('.', QuoteName(Schema_Name(t.schema_id)), 
            QuoteName(t.[name])) TName, 
            c.[name] CName,
            dt.[name] DTName,
            dt.system_type_id,
            c.max_length,
            c.precision, c.scale,
            Dense_Rank() over(order by t.[name]) Tcount,
            row_count
        from sys.columns c
        join sys.dm_db_partition_stats p on p.object_id = c.object_id and index_id < 2 and row_count > 0
        join sys.tables t on t.object_id = c.object_id and (@Schema is null or t.schema_id = Schema_Id(@Schema)) and Schema_Name(t.schema_id) != 'datasync'
        join sys.types dt on dt.user_type_id = c.user_type_id 
        where (
            (dt.system_type_id in (167,175,231,239) and (@SearchNumbers in (0,2) or (Try_Convert(int, @Find) is null and Try_Convert(decimal, @Find) is null and Try_Convert(money, @Find) is null)))
            or (@SearchNumbers > 0 and Try_Convert(int, @Find) is not null and (dt.[name] like '%int%') )
            or (@SearchNumbers > 0 and Try_Convert(decimal, @Find) is not null and (dt.[name] like '%decimal%' or dt.[name] like '%numeric%' or dt.[name] like '%real%'  or dt.[name] like '%float%') )
            or (@SearchNumbers > 0 and Try_Convert(money, @Find) is not null and (dt.[name] like '%money%') )
        )
        and (@IncludeHistory = 1 or Schema_Name(t.schema_id) != 'History' or @Schema = 'History')
        and (c.max_length >= Len(@Find) or (c.max_length = -1 and @IncludeMax = 1) or (dt.[name] not like '%char%'))
        and (@IncludeAudit = 1 or not (c.[name] = 'createdby' or c.[name] = 'alteredby'))
        and (@TnameList is null or (
                (@TExclude = 0 and exists (select * from @TNames where CharIndex(tname, t.[name]) > 0)) or 
                (@TExclude = 1 and not exists (select * from @TNames where CharIndex(tname, t.[name]) > 0))
            )
        )
        and (@CnameList is null or (
                (@CExclude = 0 and exists (select * from @CNames where CharIndex(cname, c.[name]) > 0)) or 
                (@CExclude = 1 and not exists (select * from @CNames where CharIndex(cname, c.[name]) > 0))
            )
        )
        and (@RowLimit = -1 or (@TnameList is not null and @TExclude = 0) or row_count <= @RowLimit)
        and is_computed = 0 and is_hidden = 0
    )
    /*select * from tlist order by 1,2 end;*/

    select @sql = 
        Concat(
            'insert into master.dbo.sp_FindTextInAnyTableResults(DBId, Find, Tname, Cname, Row_Count, DataType, [SQL])', 
            @br, 
            String_Agg([sql], @Union)
        ), 
        @Valid = IsNull(Max(Iif([sql] is not null, 1, 0)), 0)
    from (
        select Convert(varchar(max), 
            Concat(
                'select top(1) db_id(), '
                , '''', @Find, ''''
                , ', ''', TName, ''''
                , ', ''', CName, ''''
                , ', ', row_count
                , ', DataType = ', 
                    Concat(
                            ''''
                        , DTName
                        , case when system_type_id in (167,175,231,239) then Iif(max_length = -1, '(max)', Concat('(', max_length, ')')) end
                        , case when DTName like '%decimal%' or DTName like '%numeric%' or DTName like '%real%' or DTName like '%float%' then '(' + Concat_Ws(',', precision, scale) + ')' end
                        , ''''
                    )
                , ', SqlToRun = ',
                    Concat(''''
                        , 'Select * from '
                        , QuoteName(Db_Name()), '.', TName
                        , ' where '
                        , case when system_type_id in (167,175,231,239) then QuoteName(CName) else Concat('try_convert(varchar(50), ', QuoteName(CName), ')') end
                        , ' like ', ''''''
                        , case when system_type_id in (167,175,231,239) then @Find else Concat('%', @Find, '%') end
                        , '''''', ''''
                    )
                , ' from ', TName
                , ' where ', case when system_type_id in (167,175,231,239) then QuoteName(CName) else Concat('try_convert(varchar(50), ', QuoteName(CName), ')') end
                , ' like '''
                , case when system_type_id in (167,175,231,239) then @Find else Concat('%', @Find, '%') end,
            '''')
        )[sql]
        from TList
        where @MaxTables = -1 
            or Tcount <= @MaxTables 
            or @TnameList is not null
    )[sql];
    
    if @Debug = 1
        begin
        select @sql;
        print @sql;
        end;
    else
        if @Valid = 1 exec (@sql);
end;

select Concat(
    'Found in table '
        , Tname
        , ', Rows = ', Max(Row_Count)
        , ', Column', Iif(Count(*) > 1, 's', ''), ': '
        , String_Agg(Concat_Ws(' ', Cname, Datatype), ', ')
    ) FoundIn,
    String_Agg([SQL], ';' + Char(13)) + ';' [SQL]
from master.dbo.sp_FindTextInAnyTableResults
where DBId = Db_Id() and Find = @Find
group by Tname
order by Tname;

go
go

After creating the procedure in master register as a system procedure:

exec sys.sp_MS_marksystemobject 'sp_FindTextInAnyTable'