如何在整个数据库中查找字符串？

Question

我有一个特定的字符串，例如“123abcd”，但我不知道表的名称，甚至不知道 SQL Server 数据库上表内的列的名称。我想用 select 找到它并显示相关字符串的所有列，所以我想知道类似的事情：

select * from Database.dbo.* where * like  '%123abcd%'

出于明显的原因它不起作用，但是有一种简单的方法可以创建一个 select 语句来执行类似的操作？

Answer 1

这会起作用：

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;

不过，有一些警告。首先，这非常慢且未经优化。所有值都被简单地转换为 nvarchar，以便可以毫无错误地进行比较。您可能会遇到诸如 datetime 之类的值未按预期转换并因此在应匹配时未匹配的问题（漏报）。

WHERE (0 = 1) 的作用是使构建 OR 子句变得更加容易。如果没有匹配，您将不会得到任何行。

Answer 2

这里有更多可用于此目的的免费工具。两者都作为 SSMS 插件工作。

ApexSQL 搜索 – 100% 免费 - 搜索表中的架构和数据。有几个更有用的选项，例如依赖项跟踪...

SSMS 工具包 – 对于除 SQL 2012 之外的所有版本免费 – 没有它看起来不像以前的那么先进，但还有很多其他很酷的功能。

Answer 3

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

Answer 4

我认为你有两个选择：

1. 使用sys.tables和sys.columns构建动态SQL来执行搜索（示例）。

2. 使用任何具有此功能的程序。例如 SQL Workbench（免费）。

Answer 5

在oracle中你可以使用如下sql命令来生成你需要的sql命令：

select 
     "select * "
     " from "||table_name||
     " where "||column_name||" like '%123abcd%' ;" as sql_command
from user_tab_columns
where data_type='VARCHAR2';

Answer 6

通用资源 Grep (crgrep) 将按名称或内容在表/列中搜索字符串匹配，并支持多种数据库，包括 SQLServer、Oracle 等。完整的通配符和其他有用的选项。

它是开源的（我是作者）。

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

Answer 7

我通常使用 information_Schema.columns 和 information_schema.tables，尽管像 @yuck 所说，sys.tables 和 sys.columns code> 的输入时间较短。

在循环中，连接这些

@sql = @sql + 'select' + column_name + 
' from ' + table_name + 
' where ' + column_name ' like ''%''+value+''%' UNION

然后执行生成的 sql。

Answer 8

这是一个简单方便的基于光标的解决方案

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

Answer 9

这是 @Raihan 的 PostgreSQL 版本答案，位于 https://stackoverflow.com/a/8436114/5869805 。它还利用了 https://stackoverflow.com/a/58319308/5869805 上的答案。

SELECT 
     'select * from ' || table_name || ' where ' || column_name || ' like ''%123abcd%'';' as sql_command
FROM
(
    SELECT
        pg_class.relname AS table_name,
        pg_attribute.attname AS column_name,
        pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type
    FROM
        pg_catalog.pg_attribute
    INNER JOIN
        pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
    INNER JOIN
        pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    WHERE
        pg_attribute.attnum > 0
        AND NOT pg_attribute.attisdropped
        -- uncomment below filter if you want to check only the tables in a specific schema
        -- AND pg_namespace.nspname = '<your-schema-name-here>'
        AND pg_class.relname NOT LIKE 'IX%'
    ORDER BY
        1,2,3 ASC) tmp
where data_type NOT IN ('uuid', 'integer', 'boolean', 'bigint', 'real', 'timestamp without time zone');