通过搜索SQL Server中的所有表来查找字符串

有没有办法在SQL Server数据库的所有表中搜索字符串?

我想搜索字符串john。结果应该显示包含john的表及其各自的行。

524330 次浏览
小开

有点晚了，但希望有用。

为什么不尝试一些可以集成到SSMS中的第三方工具呢?

我曾使用ApexSQL搜索(100%免费)，在模式和数据搜索方面都取得了良好的成功，而且还有SSMS工具包具有此功能(SQL 2012不是免费的，但相当实惠)。

上面的存储过程真的很棒;只是在我看来这样更方便。此外，如果你想搜索datetime列或GUID列等，它将需要一些轻微的修改…

小开

为SQL server 2012更新TechDo的答案。你需要更改:'FROM ' + @TableName + ' (NOLOCK) ' 来 FROM ' + @TableName + 'WITH (NOLOCK) ' +

否则你会得到以下错误:Deprecated feature 'Table hint without WITH' is not supported in this version of SQL Server.

下面是更新后的完整存储过程:

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


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', 'int', 'decimal')
AND    QUOTENAME(COLUMN_NAME) > @ColumnName
)


IF @ColumnName IS NOT NULL


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


SELECT ColumnName, ColumnValue FROM #Results
END
小开
最佳答案

如果您像我一样，在生产环境中有某些限制，那么您可能希望使用表变量而不是临时表，使用临时查询而不是创建过程。

当然，根据您的sql server实例，它必须支持表变量。

我还添加了一个USE语句来缩小搜索范围

USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (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', 'int', 'decimal')
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
小开

我已经为这个写了一个SP，它以表名的形式返回搜索结果，在其中找到搜索关键字字符串的列名以及搜索对应的行，如下面的屏幕截图所示。

Sample Search Result .

这可能不是最有效的解决方案，但您可以根据需要随时修改和使用它。

IF OBJECT_ID('sp_KeywordSearch', 'P') IS NOT NULL
DROP PROC sp_KeywordSearch
GO


CREATE PROCEDURE sp_KeywordSearch @KeyWord NVARCHAR(100)
AS
BEGIN
DECLARE @Result TABLE
(TableName NVARCHAR(300),
ColumnName NVARCHAR(MAX))


DECLARE @Sql NVARCHAR(MAX),
@TableName NVARCHAR(300),
@ColumnName NVARCHAR(300),
@Count INT


DECLARE @tableCursor CURSOR


SET @tableCursor = CURSOR LOCAL SCROLL FOR
SELECT  N'SELECT @Count = COUNT(1) FROM [dbo].[' + T.TABLE_NAME + '] WITH (NOLOCK) WHERE CAST([' + C.COLUMN_NAME +
'] AS NVARCHAR(MAX)) LIKE ''%' + @KeyWord + N'%''',
T.TABLE_NAME,
C.COLUMN_NAME
FROM    INFORMATION_SCHEMA.TABLES AS T WITH (NOLOCK)
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C WITH (NOLOCK)
ON      T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
T.TABLE_NAME = C.TABLE_NAME
WHERE   T.TABLE_TYPE = 'BASE TABLE' AND
C.TABLE_SCHEMA = 'dbo' AND
C.DATA_TYPE NOT IN ('image', 'timestamp')


OPEN @tableCursor
FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName


WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Count = 0


EXEC sys.sp_executesql
@Sql,
N'@Count INT OUTPUT',
@Count OUTPUT


IF @Count > 0
BEGIN
INSERT  INTO @Result
(TableName, ColumnName)
VALUES  (@TableName, @ColumnName)
END


FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
END


CLOSE @tableCursor
DEALLOCATE @tableCursor


SET @tableCursor = CURSOR LOCAL SCROLL FOR
SELECT  SUBSTRING(TB.Sql, 1, LEN(TB.Sql) - 3) AS Sql, TB.TableName, SUBSTRING(TB.Columns, 1, LEN(TB.Columns) - 1) AS Columns
FROM    (SELECT R.TableName, (SELECT R2.ColumnName + ', ' FROM @Result AS R2 WHERE R.TableName = R2.TableName FOR XML PATH('')) AS Columns,
'SELECT * FROM ' + R.TableName + ' WITH (NOLOCK) WHERE ' +
(SELECT 'CAST(' + R2.ColumnName + ' AS NVARCHAR(MAX)) LIKE ''%' + @KeyWord + '%'' OR '
FROM   @Result AS R2
WHERE  R.TableName = R2.TableName
FOR
XML PATH('')) AS Sql
FROM   @Result AS R
GROUP BY R.TableName) TB
ORDER BY TB.Sql


OPEN @tableCursor
FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName


WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @Sql
SELECT  @TableName AS [Table],
@ColumnName AS Columns
EXEC(@Sql)


FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
END


CLOSE @tableCursor
DEALLOCATE @tableCursor


END
小开

有点晚了，但是您可以很容易地用这个查询找到一个字符串

DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)


SET @search_string = 'StringtoSearch'


DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE  type = 'U'


OPEN tables_cur


FETCH NEXT FROM tables_cur INTO @table_name, @table_id


WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id
AND system_type_id IN (167, 175, 231, 239)


OPEN columns_cur


FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + ']
LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''


EXECUTE(@sql_string)


FETCH NEXT FROM columns_cur INTO @column_name
END


CLOSE columns_cur


DEALLOCATE columns_cur


FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END


CLOSE tables_cur
DEALLOCATE tables_cur
小开

这很有帮助。我想把这个函数导入到Postgre SQL数据库。我想把它分享给感兴趣的人。会有几个小时。注意:这个函数创建了一个SQL语句列表，可以在Postgre数据库上复制和执行。也许比我聪明的人可以让Postgre在一个函数中创建并执行所有语句。

CREATE OR REPLACE FUNCTION SearchAllTables(_search text) RETURNS TABLE( txt text ) as $funct$
DECLARE __COUNT int;
__SQL text;
BEGIN
EXECUTE 'SELECT COUNT(0) FROM INFORMATION_SCHEMA.COLUMNS
WHERE    DATA_TYPE = ''text''
AND          table_schema = ''public'' ' INTO __COUNT;


RETURN QUERY
SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY table_name) < __COUNT THEN
'SELECT ''' || table_name ||'.'|| column_name || ''' AS tbl, "'  || column_name || '" AS col FROM "public"."' || "table_name" || '" WHERE "'|| "column_name" || '" ILIKE ''%' || _search  || '%'' UNION ALL'
ELSE
'SELECT ''' || table_name ||'.'|| column_name || ''' AS tbl, "'  || column_name || '" AS col FROM "public"."' || "table_name" || '" WHERE "'|| "column_name" || '" ILIKE ''%' || _search  || '%'''
END AS txt


FROM     INFORMATION_SCHEMA.COLUMNS
WHERE    DATA_TYPE = 'text'
AND          table_schema = 'public';
END
$funct$ LANGUAGE plpgsql;
小开

在这篇文章中已经提到过几次的答案，我已经采用了一点，因为我也只需要在一个表中搜索:

(并且使表名的输入更简单一点)

ALTER PROC dbo.db_compare_SearchAllTables_sp
(
@SearchStr nvarchar(100),
@TableName nvarchar(256) = ''
)
AS
BEGIN
if PARSENAME(@TableName, 2) is null
set @TableName = 'dbo.' + QUOTENAME(@TableName, '"')


declare @results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))


SET NOCOUNT ON


DECLARE @ColumnName nvarchar(128) = '', @SearchStr2 nvarchar(110)
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
IF @TableName <> ''
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 + ' WITH (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
ELSE
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 + ' WITH (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END


SELECT ColumnName, ColumnValue FROM @results
END
小开

不需要嵌套循环(遍历表的外部循环和遍历所有表列的内部循环)。可以从INFORMATION_SCHEMA.COLUMNS中检索所有(或任意选择/过滤的)表列组合，并在一个循环中简单地遍历(搜索)所有它们:

DECLARE @search VARCHAR(100), @table SYSNAME, @column SYSNAME


DECLARE curTabCol CURSOR FOR
SELECT c.TABLE_SCHEMA + '.' + c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_TYPE='BASE TABLE' -- avoid views
WHERE c.DATA_TYPE IN ('varchar','nvarchar') -- searching only in these column types
--AND c.COLUMN_NAME IN ('NAME','DESCRIPTION') -- searching only in these column names


SET @search='john'


OPEN curTabCol
FETCH NEXT FROM curTabCol INTO @table, @column


WHILE (@@FETCH_STATUS = 0)
BEGIN
EXECUTE('IF EXISTS
(SELECT * FROM ' + @table + ' WHERE ' + @column + ' = ''' + @search + ''')
PRINT ''' + @table + '.' + @column + '''')
FETCH NEXT FROM curTabCol INTO @table, @column
END


CLOSE curTabCol
DEALLOCATE curTabCol
小开

改进来自@Brandon的惊人答案，我使用类型转换向ntext和xml添加了类型:

BEGIN TRAN


DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (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', 'int', 'decimal', 'ntext', 'xml')
AND    QUOTENAME(COLUMN_NAME) > @ColumnName
)


IF @ColumnName IS NOT NULL


BEGIN
INSERT INTO @Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT((cast(' + @ColumnName + ' as nvarchar(max))), 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE (cast(' + @ColumnName + ' as nvarchar(max))) LIKE ' + @SearchStr2
)
END
END
END


SELECT ColumnName, ColumnValue FROM @Results


ROLLBACK
小开 
[CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS
BEGIN
            

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
    

EXEC SearchAllTables 'Computer'][1]




[1]: https://koukia.ca/search-for-a-string-in-all-tables-and-all-fields-of-a-database-with-tsql-b6f8a1bfda37
小开

不是要抹杀@Brandon的精彩回答，但是OP要求“结果应该显示表和它们各自的行"”我基于来自@Brandon的原始答案创建了一个记录级解决方案，该解决方案还通过简单地将所有适当的列连接到一个字符串中来避免使用其他人在这里使用的游标。(我感觉这样做会更好，但我真的没有比较过结果-请随意这样做并在这里提供反馈。)

注意，我使用了STRING_AGG函数来连接列。如果你使用的是旧版本的SQL Server，你可能需要使用可以在网上找到的替代方法之一。

enter image description here

CREATE PROC [dbo].[SearchAllTablesByRecord]
(
@SearchStr NVARCHAR(100)
)
AS
BEGIN


SET NOCOUNT ON


DECLARE @TableName NVARCHAR(256), @SearchStr2 NVARCHAR(110)
DECLARE @ColumnList NVARCHAR(2048), @ColumnJoin NVARCHAR(2048)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')


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     QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND     OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
            

PRINT @TableName    -- For progress monitoring
SET @ColumnList =
(
SELECT STRING_AGG(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')
)
SET @ColumnJoin = REPLACE(@ColumnList,',','+')
            

IF @ColumnList <> ''
EXEC
(
'SELECT ''' + @TableName + ''' AS [Table],' + @ColumnList + '
INTO #Results
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnJoin + ' LIKE ' + @SearchStr2 + '
                        

IF @@ROWCOUNT > 0 SELECT * FROM #Results'
)
END
END
小开

已经有很多可行的答案了。我只是想加上一个我想到的，有很多可选功能。

--=======================================================================
--  MSSQL Unified Search
--  Minimum compatibility level = 130 (SQL Server 2016)
--      NOTE: The minimum compatibility level is required by the built-in STRING_SPLIT() function.
--          However, you can create the STRING_SPLIT() function at the bottom of this script for
--          lower versions of MSSQL Server.
--
--  Usage:
--      Set the parameters below and execute this script.
--
/************************ Enter Parameters Here ************************/
/**/
/**/    DECLARE @SearchString VARCHAR(1000) = 'string to search for';  -- Accepts SQL wilcards
/**/
/**/    DECLARE @IncludeUserTables BIT = 1;
/**/    DECLARE @IncludeViews BIT = 0;
/**/    DECLARE @IncludeStoredProcedures BIT = 0;
/**/    DECLARE @IncludeFunctions BIT = 0;
/**/    DECLARE @IncludeTriggers BIT = 0;
/**/
/**/    DECLARE @DebugMode BIT = 0;
/**/    DECLARE @ExcludeColumnTypes NVARCHAR(500) = 'text, ntext, char, nchar, timestamp, bigint, tinyint, smallint, bit, date, time, smalldatetime, datetime, datetime2, real, money, float, decimal, binary, varbinary, image';  -- Comma delimited list
/**/
/***********************************************************************/




SET NOCOUNT ON;
SET @SearchString = QUOTENAME(@SearchString,'''');


DECLARE @Results TABLE ([ObjectType] NVARCHAR(200), [ObjectName] NVARCHAR(200), [ColumnName] NVARCHAR(400), [Value] NVARCHAR(MAX), [SelectStatement] NVARCHAR(1000));
DECLARE @ExcludeColTypes TABLE (system_type_id INT);


INSERT INTO @ExcludeColTypes ([system_type_id])
SELECT [system_type_id]
FROM sys.types WHERE
[name] IN (
SELECT LTRIM(RTRIM([value])) FROM STRING_SPLIT(@ExcludeColumnTypes,',')
);


DECLARE @ObjectType NVARCHAR(200);
DECLARE @ObjectName NVARCHAR(200);
DECLARE @Value NVARCHAR(MAX);
DECLARE @SelectStatement NVARCHAR(1000);
DECLARE @Query NVARCHAR(4000);




/********************* Table Objects *********************/
IF (@IncludeUserTables = 1)
BEGIN
DECLARE @TableObjectId INT = (SELECT MIN([object_id]) FROM sys.tables);
DECLARE @ColumnId INT;
WHILE @TableObjectId IS NOT NULL
BEGIN
    

SELECT @ObjectType = 'USER TABLE';
SELECT @ObjectName = '[' + SCHEMA_NAME([schema_id]) + '].[' + OBJECT_NAME(@TableObjectId) + ']' FROM sys.tables WHERE [object_id] = @TableObjectId;


SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId);
WHILE @ColumnId IS NOT NULL
BEGIN


SELECT @Value = '[' + [name] +']' FROM sys.columns WHERE [object_id] = @TableObjectId AND column_id = @ColumnId;


SET @SelectStatement = 'SELECT * FROM ' + @ObjectName + ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';


SET @Query = 'SELECT '
+ QUOTENAME(@ObjectType, '''')
+ ', ' + QUOTENAME(@ObjectName, '''')
+ ', ' + QUOTENAME(@Value, '''')
+ ', ' + @Value
+ ', ''' + REPLACE(@SelectStatement,'''','''''') + ''''
+ ' FROM ' + @ObjectName
+ ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';


IF @DebugMode = 0
BEGIN
INSERT INTO @Results EXEC(@Query);
END;
ELSE
BEGIN
PRINT 'Select Statement:  ' + @SelectStatement;
PRINT 'Query:  ' + @Query;
END;


SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId AND [column_id] > @ColumnId);
END;


SET @TableObjectId = (SELECT MIN([object_id]) FROM sys.tables WHERE [object_id] > @TableObjectId);
END;
END;


/********************* Objects Other than Tables *********************/
SET @Query = 'SELECT ' +
'ObjectType = CASE ' +
'WHEN b.[type] = ''V'' THEN ''VIEW'' ' +
'WHEN b.[type] = ''P'' THEN ''STORED PROCEDURE'' ' +
'WHEN b.[type] = ''FN'' THEN ''SCALAR-VALUED FUNCTION'' ' +
'WHEN b.[type] = ''IF'' THEN ''TABLE-VALUED FUNCTION'' ' +
'WHEN b.[type] = ''TR'' THEN ''TRIGGER'' ' +
'END ' +
',[ObjectName] = ''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'' ' +
',[ColumnName] = NULL ' +
',[Value] = a.[definition] ' +
',[SelectStatement] = ''SP_HELPTEXT '' + QUOTENAME(''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'','''''''') + '';'' ' +
'FROM [sys].[sql_modules] a ' +
'JOIN [sys].[objects] b ON a.[object_id] = b.[object_id] ' +
'WHERE ' +
'( ' +
'   a.[definition] LIKE ' + @SearchString +
') ' +
'AND ' +
'( ' +
'   ( ' +
CAST(@IncludeViews AS VARCHAR(1)) + ' = 1 ' +
'       AND ' +
'       b.[type] IN (''V'') ' +
'   ) ' +
'   OR ' +
'   ( ' +
CAST(@IncludeStoredProcedures AS VARCHAR(1)) + ' = 1 ' +
'       AND ' +
'       b.[type] IN (''P'') ' +
'   ) ' +
'   OR ' +
'   ( ' +
CAST(@IncludeFunctions AS VARCHAR(1)) + ' = 1 ' +
'       AND ' +
'       b.[type] IN (''FN'',''IF'') ' +
'   ) ' +
'   OR ' +
'   ( ' +
CAST(@IncludeTriggers AS VARCHAR(1)) + ' = 1 ' +
'       AND ' +
'       b.[type] IN (''TR'') ' +
'   ) ' +
'); ';


IF @DebugMode = 0
BEGIN
INSERT INTO @Results EXEC(@Query);
END;
ELSE
BEGIN
PRINT 'Select Statement:  ' + @SelectStatement;
PRINT 'Query:  ' + @Query;
END;


IF @DebugMode = 0
BEGIN
SELECT
[ObjectType]
,[ObjectName]
,[ColumnName]
,[Value]
,[Count] = CASE
WHEN [ObjectType] IN ('USER TABLE') THEN COUNT(1)
ELSE NULL
END
,[SelectStatement]
FROM @Results
GROUP BY [ObjectType], [ObjectName], [ColumnName], [Value], [SelectStatement]
ORDER BY [Value];
END;


/********************** STRING_SPLIT() FUNCTION **********************
CREATE FUNCTION STRING_SPLIT (
@Expression nvarchar(4000)
,@Delimiter nvarchar(100)
)
RETURNS @Ret TABLE ([value] NVARCHAR(4000))
AS
BEGIN


DECLARE @Start INT = 0, @End INT, @Length INT;
SELECT @End = CHARINDEX(@Delimiter,@Expression), @Length = @End - @Start;


IF @End <= 0
BEGIN
INSERT INTO @Ret ([value]) VALUES (@Expression);
END
ELSE
BEGIN
WHILE @Length >= 0
BEGIN
INSERT INTO @Ret ([value])
SELECT ltrim(rtrim(substring(@Expression,@Start,@Length)));
    

SELECT @Start = @End + LEN(@Delimiter)
SELECT @End = CHARINDEX(@Delimiter,@Expression,@Start)
IF @End < 1
SELECT @End = LEN(@Expression) + 1;
SELECT @Length = @End - @Start;
    

END;
END;
RETURN;
END;


*********************************************************************/

提交答案