SQL 如何在整个数据库中查找字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8435963/
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
How to find a string inside a entire database?
提问by Diogo
I have one specific string, such as "123abcd" for example but I don't know the name of the table or even the name of the column inside the table on my SQL Server Database. I want to find it with a select and show all columns of the related string, so I was wondering something like:
我有一个特定的字符串，例如“123abcd”，但我不知道表的名称，甚至不知道 SQL Server 数据库中表内列的名称。我想通过选择找到它并显示相关字符串的所有列，所以我想知道类似的东西：
select * from Database.dbo.* where * like '%123abcd%'
For obvious reasons it doens't work, but there is a simple way to create a select statement to do something like this?
出于显而易见的原因，它不起作用，但是有一种简单的方法可以创建一个 select 语句来执行这样的操作？
采纳答案by Yuck
This will work:
这将起作用：
DECLARE @MyValue NVarChar(4000) = 'something';
SELECT S.name SchemaName, T.name TableName
INTO #T
FROM sys.schemas S INNER JOIN
sys.tables T ON S.schema_id = T.schema_id;
WHILE (EXISTS (SELECT * FROM #T)) BEGIN
DECLARE @SQL NVarChar(4000) = 'SELECT * FROM $$TableName WHERE (0 = 1) ';
DECLARE @TableName NVarChar(1000) = (
SELECT TOP 1 SchemaName + '.' + TableName FROM #T
);
SELECT @SQL = REPLACE(@SQL, '$$TableName', @TableName);
DECLARE @Cols NVarChar(4000) = '';
SELECT
@Cols = COALESCE(@Cols + 'OR CONVERT(NVarChar(4000), ', '') + C.name + ') = CONVERT(NVarChar(4000), ''$$MyValue'') '
FROM sys.columns C
WHERE C.object_id = OBJECT_ID(@TableName);
SELECT @Cols = REPLACE(@Cols, '$$MyValue', @MyValue);
SELECT @SQL = @SQL + @Cols;
EXECUTE(@SQL);
DELETE FROM #T
WHERE SchemaName + '.' + TableName = @TableName;
END;
DROP TABLE #T;
A couple caveats, though. First, this is outrageously slow and non-optimized. All values are being converted to nvarcharsimply so that they can be compared without error. You may run into problems with values like datetimenot converting as expected and therefore not being matched when they should be (false negatives).
不过，有几个警告。首先，这是非常缓慢且未优化的。所有值都被转换为nvarchar简单的，以便它们可以无误地进行比较。您可能会遇到一些问题，例如datetime没有按预期转换，因此在应该匹配的时候没有匹配（假阴性）。
The WHERE (0 = 1)is there to make building the ORclause easier. If there are not matches you won't get any rows back.
该WHERE (0 = 1)是有使建设OR从句更容易。如果没有匹配项，您将不会得到任何行。
回答by John Moore
Here are couple more free tools that can be used for this. Both work as SSMS addins.
这里有更多免费工具可用于此目的。两者都用作 SSMS 插件。
ApexSQL Search– 100% free - searches both schema and data in tables. Has couple more useful options such as dependency tracking…
ApexSQL Search– 100% 免费 – 搜索表中的架构和数据。有几个更有用的选项，例如依赖项跟踪……
SSMS Tools pack– free for all versions except SQL 2012 – doesn't look as advanced as previous one but has a lot of other cool features.
SSMS 工具包——除 SQL 2012 之外的所有版本都是免费的——看起来不像以前的那么先进，但有很多其他很酷的功能。
回答by Neeraj Kumar Yadav
create procedure usp_find_string(@string as varchar(1000))
as
begin
declare @mincounter as int
declare @maxcounter as int
declare @stmtquery as varchar(1000)
set @stmtquery=''
create table #tmp(tablename varchar(128),columnname varchar(128),rowid int identity)
create table #tablelist(tablename varchar(128),columnname varchar(128))
declare @tmp table(name varchar(128))
declare @tablename as varchar(128)
declare @columnname as varchar(128)
insert into #tmp(tablename,columnname)
select a.name,b.name as columnname from sysobjects a
inner join syscolumns b on a.name=object_name(b.id)
where a.type='u'
and b.xtype in(select xtype from systypes
where name='text' or name='ntext' or name='varchar' or name='nvarchar' or name='char' or name='nchar')
order by a.name
select @maxcounter=max(rowid),@mincounter=min(rowid) from #tmp
while(@mincounter <= @maxcounter )
begin
select @tablename=tablename, @columnname=columnname from #tmp where rowid=@mincounter
set @stmtquery ='select top 1 ' + '[' +@columnname+']' + ' from ' + '['+@tablename+']' + ' where ' + '['+@columnname+']' + ' like ' + '''%' + @string + '%'''
insert into @tmp(name) exec(@stmtquery)
if @@rowcount >0
insert into #tablelist values(@tablename,@columnname)
set @mincounter=@mincounter +1
end
select * from #tablelist
end
回答by DavidEG
I think you have to options:
我认为你必须选择：
Build a dynamic SQL using
sys.tablesandsys.columnsto perform the search (example here).Use any program that have this function. An example of this is SQL Workbench(free).
使用
sys.tables和构建动态 SQLsys.columns以执行搜索（此处的示例）。使用任何具有此功能的程序。这方面的一个例子是SQL Workbench（免费）。
回答by Raihan
In oracle you can use the following sql command to generate the sql commands you need:
在oracle中你可以使用下面的sql命令来生成你需要的sql命令：
select
"select * "
" from "||table_name||
" where "||column_name||" like '%123abcd%' ;" as sql_command
from user_tab_columns
where data_type='VARCHAR2';
回答by MatthewMartin
I usually use information_Schema.columnsand information_schema.tables, although like @yuck said, sys.tablesand sys.columnsare shorter to type.
我通常使用information_Schema.columnsand information_schema.tables，虽然就像@yuck 说的那样，sys.tables而且sys.columns输入时间较短。
In a loop, concatenate these
在一个循环中，连接这些
@sql = @sql + 'select' + column_name +
' from ' + table_name +
' where ' + column_name ' like ''%''+value+''%' UNION
Then execute the resulting sql.
然后执行生成的sql。
回答by Craig
Common Resource Grep (crgrep) will search for string matches in tables/columns by name or content and supports a number of DBs, including SQLServer, Oracle and others. Full wild-carding and other useful options.
Common Resource Grep (crgrep) 将按名称或内容搜索表/列中的字符串匹配项，并支持许多 DB，包括 SQLServer、Oracle 等。完整的通配符和其他有用的选项。
It's opensource (I'm the author).
它是开源的（我是作者）。
回答by Gabe
SQL Locator(free) has worked great for me. It comes with a lot of options and it's fairly easy to use.
SQL Locator（免费）对我很有用。它有很多选项，而且相当容易使用。
回答by Noor A Shuvo
Here is an easy and convenient cursor based solution
这是一个简单方便的基于光标的解决方案
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 name, object_id FROM sys.objects 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
