是否有方法在SQL Server中搜索数据库中所有表格中的字符串？
我想要搜索字符串“john”。结果应该显示包含“john”的表和它们相应的行。
在SQL Server中搜索所有表以查找字符串
289
- Ramiz Raja
3
可能是在SQL Server中搜索特定值的所有表格，所有列的重复项。 - Mahmoud Gamal
2可能是重复的问题：如何在 SQL Server 数据库中查找任何值？ - Jonathan Leffler
phpMyAdmin 允许非常简单地实现此功能。 - Matoeil
13个回答
398
如果您和我一样在生产环境中有一定的限制，您可能希望使用表变量而不是临时表，并使用即席查询而不是创建过程。
当然，这取决于您的SQL服务器实例，它必须支持表变量。
我还添加了一个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
- Brandon Culley
8
25太棒了。我在第53行添加了“PRINT @TableName + '.' + @ColumnName”，以便在处理我的大型数据库时获得一些状态信息。但这非常有帮助。 - ProVega
12如果每次我来这里复制这个强大的查询语句，都能给你点赞，那么你的分数很容易就会翻倍 :D - DurkoMatko
错误 1064 (42000)：您的 SQL 语法有误；请检查与您的 MariaDB 服务器版本相对应的手册，以获取正确的语法使用方式，靠近 'DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'。 - Peter Kionga-Kamau
我遇到了一个两行错误，大约重复了十几次：
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Data$'.
有什么办法可以调试或修复它吗？ - Peak Jason1我的看法。我先后运行了存储过程方法(https://dev59.com/sGUo5IYBdhLWcg3wzCBr#19846145)和这个脚本，以观察时间差异。在我的特定数据库上，这个脚本大约需要8分钟才能完成。存储过程稍微快一些，只需5分14秒。虽然差别不是很大，但对于更大的数据库来说可能会成为一个问题。我的数据库是MS SQL Server 2016，大小为16099.94 MB。 - U A
显示剩余3条评论
49
稍微有点晚了，但希望还是有用的。
为什么不尝试一些可以集成到SSMS中的第三方工具呢？
我曾经使用过ApexSQL Search（完全免费），在模式和数据搜索方面取得了良好的效果，还有SSMS tools pack也有这个功能（对于SQL 2012不免费，但价格相当实惠）。
上面的存储过程确实很棒；只是我认为这种方式更加方便。此外，如果您想搜索日期时间列或GUID列等，则需要进行一些轻微的修改...
为什么不尝试一些可以集成到SSMS中的第三方工具呢？
我曾经使用过ApexSQL Search（完全免费），在模式和数据搜索方面取得了良好的效果，还有SSMS tools pack也有这个功能（对于SQL 2012不免费，但价格相当实惠）。
上面的存储过程确实很棒；只是我认为这种方式更加方便。此外，如果您想搜索日期时间列或GUID列等，则需要进行一些轻微的修改...
- Thomas Bovee
1
10截至2021年，Apex SQL Search不是免费的。 - Alan B
47
为了更新TechDo关于SQL Server 2012的回答，您需要将：'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
- Rafi
2
2你怎么称呼这个东西？ - Daniel L. VanDenBosch
@DanielL.VanDenBosch 您可以将此保存为存储过程，然后执行该存储过程。 - TylerH
36
虽然有点晚了，但你可以用这个查询语句很容易地找到一个字符串。
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
- Noor A Shuvo
4
这个脚本在表的模式不是"dbo"时会失败，不幸的是，大多数表在数据库中有不同的模式。对于"dbo"的几个表运行良好。 - rob
刚刚将初始光标编辑为在schemas表上进行INNER JOIN，因此在使用它时，表名中确实包含模式。 - rob
我还将99（ntext）添加到system_types列表中进行搜索，因为当前它忽略了所有的xml字段。
AND system_type_id IN (167, 175, 231, 239, 99) - rob这个脚本只是查找列名，而不是行中的内容。这不是OP所问的吗？ - Dan H.
7
不需要嵌套循环（外循环遍历表格和内循环遍历所有表列）。可以从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
- sbrbot
5
我已经为此编写了一个存储过程，它以表名的形式返回搜索结果，其中包括搜索关键字字符串所在的列名，以及如下屏幕截图所示对应行的搜索结果。
这可能不是最有效的解决方案，但您可以根据需要进行修改和使用。
这可能不是最有效的解决方案，但您可以根据需要进行修改和使用。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
- Benison Sam
2
我们在哪里定义搜索字符串？ - Dupinder Singh
嗨@DupinderSingh。以这种方式调用存储过程：
EXEC sp_KeywordSearch '2022-OH'，其中搜索字符串是2022-OH。 - Jaime Montoya2
已经有很多可行的答案了。只是想添加一个我想到的，具有许多可选功能的答案。
--=======================================================================
-- 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;
*********************************************************************/
- Paul M
2
我在想，为什么你使用了
text、ntext、char 和 nchar？它们都是用于文本的。我仍然相信这是整体上最好的解决方案。谢谢，@Paul。 - Federico Navarrete1@FedericoNavarrete SQL Server并不会将它们全部视为相同。虽然对于人类来说它们都可能是“文本”，但数据库管理系统会进行区分。该脚本基于SQL Server数据类型，而不是人类的解释方式。 - Paul M
2
不是要否定@Brandon的优秀回答，但是OP请求“结果应该显示表及其相应的行”。我基于@Brandon的原始答案创建了一个记录级解决方案，也避免了其他人在此处使用CURSORs，只需将所有适当的列连接成一个字符串即可。（我有这种感觉会表现得更好，但是老实说我还没有比较过结果-随时提供反馈。）
NB 我使用STRING_AGG函数连接列。如果您使用的是旧版SQL Server，则可能需要使用可以在网上找到的替代方法。
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
- Wayne Ivory
2
在@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
- Juanmabs22
0
这非常有帮助。我想将此函数导入到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;
- Juls
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
- 相关问题
- 102 在 SQL Server 数据库中查找所有表中特定类型的所有列
- 216 在SQL Server中搜索所有表、所有列的特定值
- 3 查询SQL Server数据字典以查找包含表“x”的所有数据库。
- 6 在SQL Server表中搜索ASCII值
- 45 如何在SQL Server中查找表的所有依赖项
- 29 SQL Server：搜索所有表格以查找特定GUID
- 141 在Oracle中搜索所有表的所有字段以查找特定值
- 13 SQL Server Management Studio - 查找所有非空表
- 57 在SQL Server Management Studio 2008中通过搜索所有存储过程来查找一个字符串
- 63 SQL Server查询以查找所有当前数据库名称