如何在整个数据库中找到一个字符串?

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来查找并显示相关字符串的所有列，所以我想知道如下内容:

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语句来完成这样的操作吗?

10 个解决方案

#1

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 nvarchar simply so that they can be compared without error. You may run into problems with values like datetime not 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 OR clause easier. If there are not matches you won't get any rows back.

WHERE(0 = 1)是使构建OR子句更容易的地方。如果没有匹配项，就不会返回任何行。

#2

Here are couple more free tools that can be used for this. Both work as SSMS addins.

这里有几个免费的工具可以用于此。两人都是SSMS addins的员工。

ApexSQL Search – 100% free - searches both schema and data in tables. Has couple more useful options such as dependency tracking…

ApexSQL搜索- 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之外的所有版本都是免费的——看起来不像以前的版本那么高级，但是有很多其他很酷的特性。

#3

I think you have to options:

我认为你必须做出选择:

Build a dynamic SQL using sys.tables and sys.columns to perform the search (example here).

使用sys构建一个动态SQL。表和系统。执行搜索的列(这里的示例)。
Use any program that have this function. An example of this is SQL Workbench (free).

使用任何具有此功能的程序。这方面的一个例子是SQL Workbench(免费)。

#4

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

#5

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';

#6

SQL Locator (free) has worked great for me. It comes with a lot of options and it's fairly easy to use.

SQL Locator(免费)对我来说非常有用。它有很多选择，而且很容易使用。

#7

I usually use information_Schema.columns and information_schema.tables, although like @yuck said, sys.tables and sys.columns are shorter to type.

我通常使用information_Schema。列和information_schema。桌子，虽然像@yuck说的那样，是sys。表和系统。列的类型更短。

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。

#8

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)将通过名称或内容搜索表/列中的字符串匹配，并支持一些DBs，包括SQLServer、Oracle和其他。完全野外梳理和其他有用的选择。

It's opensource (I'm the author).

它是opensource(我是作者)。

http://sourceforge.net/projects/crgrep/

#9

Sorry for the late answer, but I just had this question as well and ended up resolving it using another approach that is probably more generic to all databases.

不好意思，我刚刚回答了这个问题，最后我用另一种可能对所有数据库更通用的方法解决了这个问题。

create a database dump.
创建一个数据库转储。
From there you should be able to open the file in a text editor and search for the required string.
在这里，您应该能够在文本编辑器中打开文件并搜索所需的字符串。

#10

Here is a 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

#1