SQL 在数据库的所有表、行和列中搜索字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/591853/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search for a string in all tables, rows and columns of a DB
提问by Martin
I am lost in a big database and I am not able to find where the data I get comes from. I was wondering if it is possible with SQL Server 2005 to search for a string in all tables, rows and columns of a database?
我迷失在一个大数据库中，无法找到我获得的数据的来源。我想知道是否可以使用 SQL Server 2005 在数据库的所有表、行和列中搜索字符串？
Does anybody has an idea if it is possible and how?
有没有人知道它是否可能以及如何实现？
回答by Tom H
This code should do it in SQL 2005, but a few caveats:
这段代码应该在 SQL 2005 中完成，但有一些警告：
It is RIDICULOUSLY slow. I tested it on a small database that I have with only a handful of tables and it took many minutes to complete. If your database is so big that you can't understand it then this will probably be unusable anyway.
I wrote this off the cuff. I didn't put in any error handling and there might be some other sloppiness especially since I don't use cursors often. For example, I think there's a way to refresh the columns cursor instead of closing/deallocating/recreating it every time.
它非常缓慢。我在一个只有少数表的小型数据库上对其进行了测试，并且花了好几分钟才完成。如果您的数据库太大以至于您无法理解它，那么无论如何它都可能无法使用。
我在袖口上写下了这个。我没有进行任何错误处理，可能还有其他一些草率，特别是因为我不经常使用游标。例如，我认为有一种方法可以刷新列游标，而不是每次都关闭/解除分配/重新创建它。
If you can't understand the database or don't know where stuff is coming from, then you should probably find someone who does. Even if you can find where the data is, it might be duplicated somewhere or there might be other aspects of the database that you don't understand. If no one in your company understands the database then you're in a pretty big mess.
如果您不能理解数据库或不知道东西来自哪里，那么您可能应该找一个能理解的人。即使您可以找到数据所在的位置，它也可能在某处重复，或者您不了解数据库的其他方面。如果您公司中没有人了解数据库，那么您将陷入一团糟的境地。
DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_schema SYSNAME,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'Test'
DECLARE tables_cur CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_schema, @table_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @table_schema AND TABLE_NAME = @table_name AND COLLATION_NAME IS NOT NULL -- Only strings have this and they always have it
OPEN columns_cur
FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ' WHERE ' + QUOTENAME(@column_name) + ' LIKE ''%' + @search_string + '%'') PRINT ''' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ', ' + QUOTENAME(@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_schema, @table_name
END
CLOSE tables_cur
DEALLOCATE tables_cur
回答by Ken Williams
I'd suggest you find yourself a 3rd party tool for this such as ApexSQL Search(there are probably others out there too but I use this one because it's free).
我建议您为自己找到一个 3rd 方工具，例如ApexSQL Search（可能还有其他工具，但我使用这个工具是因为它是免费的）。
If you really want to go the SQL way you can try using stored procedure created by Sorna Kumar Muthuraj– copied code is below. Just execute this stored procedure for all tables in your schema (easy with dynamics SQL)
如果您真的想采用 SQL 方式，您可以尝试使用由Sorna Kumar Muthuraj创建的存储过程 ——复制代码如下。只需对架构中的所有表执行此存储过程（使用动态 SQL 即可轻松实现）
CREATE PROCEDURE SearchTables
@Tablenames VARCHAR(500)
,@SearchStr NVARCHAR(60)
,@GenerateSQLOnly Bit = 0
AS
/*
Parameters and usage
@Tablenames -- Provide a single table name or multiple table name with comma seperated.
If left blank , it will check for all the tables in the database
@SearchStr -- Provide the search string. Use the '%' to coin the search.
EX : X%--- will give data staring with X
%X--- will give data ending with X
%X%--- will give data containig X
@GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database.
By default it is 0 and it will search.
Samples :
1. To search data in a table
EXEC SearchTables @Tablenames = 'T1'
,@SearchStr = '%TEST%'
The above sample searches in table T1 with string containing TEST.
2. To search in a multiple table
EXEC SearchTables @Tablenames = 'T2'
,@SearchStr = '%TEST%'
The above sample searches in tables T1 & T2 with string containing TEST.
3. To search in a all table
EXEC SearchTables @Tablenames = '%'
,@SearchStr = '%TEST%'
The above sample searches in all table with string containing TEST.
4. Generate the SQL for the Select statements
EXEC SearchTables @Tablenames = 'T1'
,@SearchStr = '%TEST%'
,@GenerateSQLOnly = 1
*/
SET NOCOUNT ON
DECLARE @CheckTableNames Table
(
Tablename sysname
)
DECLARE @SQLTbl TABLE
(
Tablename SYSNAME
,WHEREClause VARCHAR(MAX)
,SQLStatement VARCHAR(MAX)
,Execstatus BIT
)
DECLARE @sql VARCHAR(MAX)
DECLARE @tmpTblname sysname
IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%')
BEGIN
INSERT INTO @CheckTableNames
SELECT Name
FROM sys.tables
END
ELSE
BEGIN
SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + ''''
INSERT INTO @CheckTableNames
EXEC(@sql)
END
INSERT INTO @SQLTbl
( Tablename,WHEREClause)
SELECT SCh.name + '.' + ST.NAME,
(
SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10)
FROM SYS.columns SC
JOIN SYS.types STy
ON STy.system_type_id = SC.system_type_id
AND STy.user_type_id =SC.user_type_id
WHERE STY.name in ('varchar','char','nvarchar','nchar')
AND SC.object_id = ST.object_id
ORDER BY SC.name
FOR XML PATH('')
)
FROM SYS.tables ST
JOIN @CheckTableNames chktbls
ON chktbls.Tablename = ST.name
JOIN SYS.schemas SCh
ON ST.schema_id = SCh.schema_id
WHERE ST.name <> 'SearchTMP'
GROUP BY ST.object_id, SCh.name + '.' + ST.NAME ;
UPDATE @SQLTbl
SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)
DELETE FROM @SQLTbl
WHERE WHEREClause IS NULL
WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0)
BEGIN
SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement
FROM @SQLTbl
WHERE ISNULL(Execstatus ,0) = 0
IF @GenerateSQLOnly = 0
BEGIN
IF OBJECT_ID('SearchTMP','U') IS NOT NULL
DROP TABLE SearchTMP
EXEC (@SQL)
IF EXISTS(SELECT 1 FROM SearchTMP)
BEGIN
SELECT Tablename=@tmpTblname,* FROM SearchTMP
END
END
ELSE
BEGIN
PRINT REPLICATE('-',100)
PRINT @tmpTblname
PRINT REPLICATE('-',100)
PRINT replace(@sql,'INTO SearchTMP','')
END
UPDATE @SQLTbl
SET Execstatus = 1
WHERE Tablename = @tmpTblname
END
SET NOCOUNT OFF
go
回答by Marcus Vinicius Pompeu
Although the solutions presented before are valid and work, I humbly offer a code that's cleaner, more elegant, and with better performance, at least as I see it.
尽管之前提出的解决方案有效且有效，但我谦虚地提供了一个更简洁、更优雅且性能更好的代码，至少在我看来是这样。
Firstly, one may ask: Why would anyone ever need a code snippet to globally and blindly look for a string? Hey, they already invented fulltext, don't you know?
首先，有人可能会问：为什么有人需要一个代码片段来全局盲目地寻找一个字符串？嘿，他们已经发明了全文，你不知道吗？
My answer: my mainly work is at systems integration projects, and discovering where the data is written is important whenever I'm learning a new and undocummented database, which seldom happens.
我的回答：我的主要工作是在系统集成项目中，每当我学习一个新的和未记录的数据库时，发现数据写入的位置很重要，这种情况很少发生。
Also, the code I present is a stripped down version of a more powerful and dangerous script that searches and REPLACES text throughout the database.
此外，我提供的代码是一个更强大和更危险的脚本的精简版本，该脚本在整个数据库中搜索和替换文本。
CREATE TABLE #result(
id INT IDENTITY, -- just for register seek order
tblName VARCHAR(255),
colName VARCHAR(255),
qtRows INT
)
go
DECLARE @toLookFor VARCHAR(255)
SET @toLookFor = '[input your search criteria here]'
DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
'[' + usr.name + '].[' + tbl.name + ']' AS tblName,
'[' + col.name + ']' AS colName,
LOWER(typ.name) AS typName
FROM
sysobjects tbl
INNER JOIN(
syscolumns col
INNER JOIN systypes typ
ON typ.xtype = col.xtype
)
ON col.id = tbl.id
--
LEFT OUTER JOIN sysusers usr
ON usr.uid = tbl.uid
WHERE tbl.xtype = 'U'
AND LOWER(typ.name) IN(
'char', 'nchar',
'varchar', 'nvarchar',
'text', 'ntext'
)
ORDER BY tbl.name, col.colorder
--
DECLARE @tblName VARCHAR(255)
DECLARE @colName VARCHAR(255)
DECLARE @typName VARCHAR(255)
--
DECLARE @sql NVARCHAR(4000)
DECLARE @crlf CHAR(2)
SET @crlf = CHAR(13) + CHAR(10)
OPEN cCursor
FETCH cCursor
INTO @tblName, @colName, @typName
WHILE @@fetch_status = 0
BEGIN
IF @typName IN('text', 'ntext')
BEGIN
SET @sql = ''
SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
SET @sql = @sql + 'FROM ' + @tblName + @crlf
SET @sql = @sql + 'WHERE PATINDEX(''%'' + @toLookFor + ''%'', ' + @colName + ') > 0' + @crlf
END
ELSE
BEGIN
SET @sql = ''
SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
SET @sql = @sql + 'FROM ' + @tblName + @crlf
SET @sql = @sql + 'WHERE ' + @colName + ' LIKE ''%'' + @toLookFor + ''%''' + @crlf
END
EXECUTE sp_executesql
@sql,
N'@tblName varchar(255), @colName varchar(255), @toLookFor varchar(255)',
@tblName, @colName, @toLookFor
FETCH cCursor
INTO @tblName, @colName, @typName
END
SELECT *
FROM #result
WHERE qtRows > 0
ORDER BY id
GO
DROP TABLE #result
go
回答by MikeW
If you are "getting data" from an application, the sensible thing would be to use the profiler and profile the database while running the application. Trace it, then search the results for that string.
如果您从应用程序“获取数据”，明智的做法是在运行应用程序时使用分析器并分析数据库。跟踪它，然后搜索该字符串的结果。
回答by NitroxDM
The SSMS Tools PACK Add-In (Add-On) for Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express will do exactly what you need. On larger database it takes some time to search, but that is to be expected. It also includes a ton of cool features that should have be included with SQL Server Management Studio in the first place. Give it a try www.ssmstoolspack.com/
适用于 Microsoft SQL Server Management Studio 和 Microsoft SQL Server Management Studio Express 的 SSMS 工具包插件（附加组件）将完全满足您的需求。在较大的数据库上搜索需要一些时间，但这是可以预料的。它还包括大量很酷的功能，这些功能应该首先包含在 SQL Server Management Studio 中。试一试www.ssmstoolspack.com/
You do need to have SP2 for SQL Server Management Studio installed to run the tools.
您需要安装 SP2 for SQL Server Management Studio 才能运行这些工具。
回答by Oliver
I adapted a script originally written by Narayana Vyas Kondreddi in 2002. I changed the where clause to check text/ntext fields as well, by using patindex rather than like. I also changed the results table slightly. Unreasonably, I changed variable names, and aligned as I prefer (no disrespect to Mr. Kondretti). The user may want to change the data types searched. I used a global table to allow querying mid-processing, but a permanent table might be a smarter way to go.
我改编了最初由Narayana Vyas Kondreddi 于 2002 年编写的脚本。我通过使用 patindex 而不是 like 更改了 where 子句以检查 text/ntext 字段。我还稍微更改了结果表。不合理地，我更改了变量名称，并按照我的喜好进行了对齐（没有不尊重 Kondretti 先生）。用户可能想要更改搜索的数据类型。我使用了一个全局表来允许查询中间处理，但永久表可能是一个更聪明的方法。
/* original script by Narayana Vyas Kondreddi, 2002 */
/* adapted by Oliver Holloway, 2009 */
/* these lines can be replaced by use of input parameter for a proc */
declare @search_string varchar(1000);
set @search_string = 'what.you.are.searching.for';
/* create results table */
create table ##string_locations (
table_name varchar(1000),
field_name varchar(1000),
field_value varchar(8000)
)
;
/* special settings */
set nocount on
;
/* declare variables */
declare
@table_name varchar(1000),
@field_name varchar(1000)
;
/* variable settings */
set @table_name = ''
;
set @search_string = QUOTENAME('%' + @search_string + '%','''')
;
/* for each table */
while @table_name is not null
begin
set @field_name = ''
set @table_name = (
select MIN(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name))
from INFORMATION_SCHEMA.TABLES
where
table_type = 'BASE TABLE' and
QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) > @table_name and
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)), 'IsMSShipped') = 0
)
/* for each string-ish field */
while (@table_name is not null) and (@field_name is not null)
begin
set @field_name = (
select MIN(QUOTENAME(column_name))
from INFORMATION_SCHEMA.COLUMNS
where
table_schema = PARSENAME(@table_name, 2) and
table_name = PARSENAME(@table_name, 1) and
data_type in ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext') and
QUOTENAME(column_name) > @field_name
)
/* search that field for the string supplied */
if @field_name is not null
begin
insert into ##string_locations
exec(
'select ''' + @table_name + ''',''' + @field_name + ''',' + @field_name +
'from ' + @table_name + ' (nolock) ' +
'where patindex(' + @search_string + ',' + @field_name + ') > 0' /* patindex works with char & text */
)
end
;
end
;
end
;
/* return results */
select table_name, field_name, field_value from ##string_locations (nolock)
;
/* drop temp table */
--drop table ##string_locations
;
回答by Maksym Gontar
Actually Im agree with MikeW (+1) it's better to use profiler for this case.
实际上我同意 MikeW (+1) 在这种情况下最好使用分析器。
Anyway, if you really need to grab all (n)varchar columns in db and make a search. See below. I suppose to use INFORMATION_SCHEMA.Tables + dynamic SQL. The plain search:
无论如何，如果您真的需要获取 db 中的所有 (n)varchar 列并进行搜索。见下文。我想使用 INFORMATION_SCHEMA.Tables + 动态 SQL。普通搜索：
DECLARE @SearchText VARCHAR(100)
SET @SearchText = '12'
DECLARE @Tables TABLE(N INT, TableName VARCHAR(100), ColumnNamesCSV VARCHAR(2000), SQL VARCHAR(4000))
INSERT INTO @Tables (TableName, ColumnNamesCSV)
SELECT T.TABLE_NAME AS TableName,
( SELECT C.Column_Name + ','
FROM INFORMATION_SCHEMA.Columns C
WHERE T.TABLE_NAME = C.TABLE_NAME
AND C.DATA_TYPE IN ('nvarchar','varchar')
FOR XML PATH('')
)
FROM INFORMATION_SCHEMA.Tables T
DELETE FROM @Tables WHERE ColumnNamesCSV IS NULL
INSERT INTO @Tables (N, TableName, ColumnNamesCSV)
SELECT ROW_NUMBER() OVER(ORDER BY TableName), TableName, ColumnNamesCSV
FROM @Tables
DELETE FROM @Tables WHERE N IS NULL
UPDATE @Tables
SET ColumnNamesCSV = SUBSTRING(ColumnNamesCSV, 0, LEN(ColumnNamesCSV))
UPDATE @Tables
SET SQL = 'SELECT * FROM ['+TableName+'] WHERE '''+@SearchText+''' IN ('+ColumnNamesCSV+')'
DECLARE @C INT,
@I INT,
@SQL VARCHAR(4000)
SELECT @I = 1,
@C = COUNT(1)
FROM @Tables
WHILE @I <= @C BEGIN
SELECT @SQL = SQL FROM @Tables WHERE N = @I
SET @I = @I+1
EXEC(@SQL)
END
and one with LIKE clause:
一个带有 LIKE 子句：
DECLARE @SearchText VARCHAR(100)
SET @SearchText = '12'
DECLARE @Tables TABLE(N INT, TableName VARCHAR(100), ColumnNamesCSVLike VARCHAR(2000), LIKESQL VARCHAR(4000))
INSERT INTO @Tables (TableName, ColumnNamesCSVLike)
SELECT T.TABLE_NAME AS TableName,
( SELECT C.Column_Name + ' LIKE ''%'+@SearchText+'%'' OR '
FROM INFORMATION_SCHEMA.Columns C
WHERE T.TABLE_NAME = C.TABLE_NAME
AND C.DATA_TYPE IN ('nvarchar','varchar')
FOR XML PATH(''))
FROM INFORMATION_SCHEMA.Tables T
DELETE FROM @Tables WHERE ColumnNamesCSVLike IS NULL
INSERT INTO @Tables (N, TableName, ColumnNamesCSVLike)
SELECT ROW_NUMBER() OVER(ORDER BY TableName), TableName, ColumnNamesCSVLike
FROM @Tables
DELETE FROM @Tables WHERE N IS NULL
UPDATE @Tables
SET ColumnNamesCSVLike = SUBSTRING(ColumnNamesCSVLike, 0, LEN(ColumnNamesCSVLike)-2)
UPDATE @Tables SET LIKESQL = 'SELECT * FROM ['+TableName+'] WHERE '+ColumnNamesCSVLike
DECLARE @C INT,
@I INT,
@LIKESQL VARCHAR(4000)
SELECT @I = 1,
@C = COUNT(1)
FROM @Tables
WHILE @I <= @C BEGIN
SELECT @LIKESQL = LIKESQL FROM @Tables WHERE N = @I
SET @I = @I +1
EXEC(@LIKESQL)
END
回答by JohnB
Other answers posted already may work equally well or better, but I haven't used them. However, the following SQL I have used, and it really helped me out when I was trying to reverse-engineer a big system with a huge (and very unorganzied) SQL Server database.
已经发布的其他答案可能同样有效或更好，但我没有使用它们。但是，我使用了以下 SQL，当我尝试对具有庞大（且非常无组织）的 SQL Server 数据库的大型系统进行逆向工程时，它确实帮助了我。
This isn't my code. I wish I could credit the original author, but I can't find the link to the article anymore :(
这不是我的代码。我希望我能归功于原作者，但我再也找不到这篇文章的链接了:(
Use
go
declare @SearchChar varchar(8000)
Set @SearchChar = -- Like 'A%', '11/11/2006'
declare @CMDMain varchar(8000), @CMDMainCount varchar(8000),@CMDJoin varchar(8000)
declare @ColumnName varchar(100),@TableName varchar(100)
declare dbTable cursor for
SELECT
Distinct b.Name as TableName
FROM
sysobjects b
WHERE
b.type='u' and b.Name 'dtproperties'
order by b.name
open dbTable
fetch next from dbTable into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
declare db cursor for
SELECT
c.Name as ColumnName
FROM
sysobjects b,
syscolumns c
WHERE
C.id = b.id and
b.type='u' and b.Name = @TableName
order by b.name
open db
fetch next from db into @ColumnName
set @CMDMain = 'SELECT ' + char(39) + @TableName + char(39) + ' as TableName,'+
' ['+ @TableName + '].* FROM [' + @TableName + ']'+
' WHERE '
set @CMDMainCount = 'SELECT Count(*) FROM [' + @TableName + '] Where '
Set @CMDJoin = ''
WHILE @@FETCH_STATUS = 0
BEGIN
set @CMDJoin = @CMDJoin + 'Convert(varchar(5000),[' +@ColumnName + ']) like ' + char(39) + @SearchChar + char(39) + ' OR '
fetch next from db into @ColumnName
end
close db
deallocate db
Set @CMDMainCount = 'If ('+ @CMDMainCount + Left(@CMDJoin, len(@CMDJoin) - 3)+ ') > 0 Begin '
Set @CMDMain = @CMDMainCount + @CMDMain + Left(@CMDJoin, len(@CMDJoin) - 3)
Set @CMDMain = @CMDMain + ' End '
Print @CMDMain
exec (@CMDMain)
fetch next from dbTable into @TableName
end
close dbTable
deallocate dbTable回答by Edwin Stoteler
This uses no cursors or anything like that, just one dynamic query.
这不使用游标或类似的东西，只使用一个动态查询。
Also note that this uses LIKE. Since that happened to be what I needed. It works for all schemas, all tables and only query's those columns that are NVARCHARor VARCHAReven if they have UDDT.
另请注意，这使用LIKE. 因为那恰好是我所需要的。它适用于所有模式、所有表，并且只查询那些具有 UDDTNVARCHAR或VARCHAR即使它们具有 UDDT 的列。
DECLARE @searchstring NVARCHAR(255)
SET @searchstring = '%searchstring%'
DECLARE @sql NVARCHAR(max)
SELECT @sql = STUFF((
SELECT ' UNION ALL SELECT ''' + TABLE_NAME + ''' AS tablename, ''' + COLUMN_NAME + ''' AS columnname, ' + COLUMN_NAME + ' AS valuename' +
' FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME +
' WHERE ' + COLUMN_NAME + ' LIKE ''' + @searchstring + ''''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE in ('nvarchar', 'varchar')
FOR XML PATH('')
) ,1, 11, '')
EXEC(@sql)
The output gives you the table, column and value. Time to execute on a small database was ~3 seconds, had about 3000 results.
输出为您提供表、列和值。在小型数据库上执行的时间约为 3 秒，有大约 3000 个结果。
回答by Manish
@NLwino, yery good query with a few errors for keyword usage. I had to modify it a little to wrap the keywords with [ ] and also look char and ntext columns.
@NLwino，很好的查询，关键字使用有一些错误。我不得不稍微修改一下，用 [] 包裹关键字，并查看 char 和 ntext 列。
DECLARE @searchstring NVARCHAR(255)
SET @searchstring = '%WDB1014%'
DECLARE @sql NVARCHAR(max)
SELECT @sql = STUFF((
SELECT ' UNION ALL SELECT ''' + TABLE_NAME + ''' AS tbl, ''' + COLUMN_NAME + ''' AS col, [' + COLUMN_NAME + '] AS val' +
' FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME +
'] WHERE [' + COLUMN_NAME + '] LIKE ''' + @searchstring + ''''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE in ('nvarchar', 'varchar', 'char', 'ntext')
FOR XML PATH('')
) ,1, 11, '')
Exec (@sql)
I ran it on 2.5 GB database and it came back in 51 seconds
我在 2.5 GB 的数据库上运行它，它在 51 秒内恢复
