搜索表格中所有列以查找一个值？

Question

搜索表格中所有列以查找一个值？

sqlsql-server

9

我寻找了答案，但是所有我找到的都是人们问如何在数据库中搜索所有表的所有列的值。我只想搜索特定表的所有列。人们为所有表问题想出的代码很复杂，让我很难弄清楚它究竟在哪里搜索特定的表。有人能帮帮我吗？谢谢。

- Jesse

1

我们需要知道数据类型是什么，以及为什么你的结构会允许这种情况？难道所有字段都是 varchar 或者其他类型吗？ - JNK

1

请问使用的 SQL Server 版本是哪个？ - Aaron Bertrand

9个回答

2

我不知道你正在搜索哪些列类型或数据值，但我猜测你正在尝试在多个文本列中搜索子字符串。

这就是全文搜索的工作。

不要浪费时间用 LIKE '%' + @SearchStr + '%'。你必须编写许多复杂的代码来支持它，而且那种解决方案的性能也不好。

- Bill Karwin

如果全文搜索只是应用程序的维护或内部任务，而不是主要功能（或根本不是最终用户的功能），那么设置全文搜索可能会过度。 - Aaron Bertrand

这仍然比编写长而复杂的存储过程来运行动态SQL查询要容易，就像@Decker97的回答中所述。 - Bill Karwin

这取决于SQL Server的版本以及在安装SQL Server时是否安装了全文本。我没有尝试过，但我读过有关事后强行添加的可怕故事。不过，即使这是一个内部任务，您是否打算为每个表中的每个字符串列填充全文本索引？在我看来，这是一项昂贵的成本/效益考虑。 - Aaron Bertrand

我假设使用MS SQL Server 2005或更高版本，其中包括全文搜索功能。它不必被硬塞进去。OP并不需要在每个表的每个列上建立索引；他明确表示这是针对一个表中的一组列。 - Bill Karwin

如果我们谈论的是 MS SQL Server 2000：我认为回答任何这样的问题都应该是“如果你想使用高级功能，你应该升级。” - Bill Karwin

显示剩余2条评论

2

在一个类似的问题中，我提到了SQL Workbench/J。

搜索数据库的命令也可以限制为只针对一张表。所以即使那个问题是特定于PostgreSQL的，据我所知，该工具也适用于SQL Server。

- user330315

1

我修改了这个存储过程，使其接受一个表名作为第二个参数，并仅在该表中搜索数据：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchOneTable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SearchOneTable]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[SearchOneTable]
(
    @SearchStr nvarchar(100) = 'A',
    @TableName nvarchar(256) = 'dbo.Alerts'
)
AS
BEGIN

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    --SET NOCOUNT ON

    DECLARE @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
    --SET @SearchStr2 = QUOTENAME(@SearchStr, '''') --exact match
    SET @ColumnName = ' '


        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')
                    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 
    SELECT ColumnName, ColumnValue FROM #Results
END


GO

- Decker97

这不会搜索text或xml数据类型，而对于这样的要求，谁知道他们的表中可能有什么！ - JNK

该操作提到他发现了搜索整个数据库的存储过程，但是他在理解它们方面遇到了困难。如果这只是供开发人员使用，我认为修改一个动态解决方案以满足单个表格的要求并没有问题。 - Decker97

我也是，只是指出来而已。注意，我没有给你点踩 :) - JNK

1

这里有一个解决方案，类似于@Decker97的方法，从元数据中找出哪些列适合进行字符串搜索。假设是2005+版本。支持text/ntext（虽然您不应该再使用它们了），char/nchar/varchar/nvarchar，甚至在适当的情况下将前导N放在搜索字符串上。不支持xml列。

它所做的略有不同之处在于，它为每个表返回单个结果集，而不是为每个单独的列返回结果集，因此无论有多少列匹配，输出只有一行匹配结果。

DECLARE @SearchTerm nvarchar(255) = N'foo',
        @TableName  nvarchar(128) = NULL,
        @sql        nvarchar(max) = N'';

;WITH tables(obj_name, obj_id, columns) AS
(
  SELECT obj_name = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name),
         obj_id   = [object_id],
         columns  = (
             SELECT N',' + QUOTENAME(c.name)
               FROM sys.columns AS c 
               WHERE c.[object_id] = t.[object_id]
               ORDER BY c.column_id FOR XML PATH(N''), 
               TYPE).value(N'./text()[1]', N'nvarchar(max)')
    FROM sys.tables AS t INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id]
    WHERE (t.name = @TableName OR @TableName IS NULL)
    AND EXISTS
    (
      SELECT 1 FROM sys.columns AS c
        WHERE c.[object_id] = t.[object_id]
        AND c.system_type_id IN (35,99,167,175,231,239)
    )
)
SELECT @sql += N'SELECT N' + char(39) 
  + REPLACE(obj_name, char(39), char(39) + char(39))
  + char(39) + columns + N' FROM ' + obj_name + N' WHERE ' 
  + STUFF((
      SELECT N' OR ' + QUOTENAME(name) + N' LIKE ' + CASE 
        WHEN c.system_type_id IN (99,231,239) 
        THEN 'N' ELSE N'' END
        + char(39) + N'%' + @SearchTerm + N'%' + char(39)
        FROM sys.columns AS c WHERE c.[object_id] = tables.obj_id
        AND c.system_type_id IN (35,99,167,175,231,239)
        ORDER BY name FOR XML PATH(''), TYPE
     ).value(N'./text()[1]', N'nvarchar(max)') 
      + char(59) + char(13) + char(10), 1, 4, N'')
FROM tables;

PRINT @sql;
--EXEC sys.sp_executeSQL @sql;

根据您系统中可搜索列的数量，PRINT 不一定会显示完整命令，您可能会认为代码存在错误（或者至少是 PRINT 存在截断文本的 bug）。您可以在 SSMS 设置中增加结果到文本输出的大小，但这仍然不够。您可以使用 SELECT CONVERT(xml, @sql); 代替（有关更多信息，请参见 this tip）。

如果您使用的是 SQL Server 2017 或更高版本

新函数 STRING_AGG() 可以大大简化代码，如果您有很多现有代码使用 FOR XML PATH 连接字符串，则在重新访问它们时将其更新为更现代的方法可能会很有用。因此，以下是使用 STRING_AGG() 的版本：

DECLARE @SearchTerm nvarchar(255) = N'foo',
        @TableName  nvarchar(128) = NULL,
        @sql        nvarchar(max) = N'';

;WITH tables(obj_name, obj_id, columns) AS
(
  SELECT obj_name = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name),
         obj_id   = [object_id],
         columns  = (SELECT STRING_AGG(QUOTENAME(c.name), N',')
           WITHIN GROUP (ORDER BY c.column_id) 
           FROM sys.columns AS c WHERE c.[object_id] = t.[object_id]
             AND c.system_type_id IN (35,99,167,175,231,239))
    FROM sys.tables AS t INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id]
    WHERE (t.name = @TableName OR @TableName IS NULL)
)
SELECT @sql += N'SELECT N' + char(39) 
  + REPLACE(obj_name, char(39), char(39) + char(39))
  + char(39) + N',' + columns + N' FROM ' + obj_name + N' WHERE ' 
  + (SELECT STRING_AGG(QUOTENAME(name) + N' LIKE ' + CASE 
        WHEN c.system_type_id IN (99,231,239) 
        THEN 'N' ELSE N'' END
        + char(39) + N'%' + @SearchTerm + N'%' + char(39),      
     N' OR ') + N';' + char(13) + char(10)
        FROM sys.columns AS c WHERE c.[object_id] = tables.obj_id
        AND c.system_type_id IN (35,99,167,175,231,239))
FROM tables WHERE columns IS NOT NULL;

PRINT @sql;
--EXEC sys.sp_executeSQL @sql;

更多动态SQL资源

- Aaron Bertrand

0

我遇到了这个问题，通常是在从CSV文件上传数据后，我不得不修改文本字段中的逗号“，”，以便数据可以正确加载。一旦在SQL Server中，就需要将修改后的字符更改回逗号，并且能够搜索整个表格非常有帮助。Greg Robidoux在mssqltips上发布了一个存储过程，可以搜索指定表格的列以查找特定字符串值。您可以在此处找到它以及不使用游标的SPROC和更多详细信息：

https://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/

我已经在下面发布了原始的 SPROC：

USE master 
GO 

CREATE PROCEDURE dbo.sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname 
AS 

DECLARE @sqlCommand VARCHAR(8000) 
DECLARE @where VARCHAR(8000) 
DECLARE @columnName sysname 
DECLARE @cursor VARCHAR(8000) 

BEGIN TRY 
   SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE' 
   SET @where = '' 

   SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME 
   FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = ''' + @schema + ''' 
   AND TABLE_NAME = ''' + @table + ''' 
   AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')' 

   EXEC (@cursor) 

   OPEN col_cursor    
   FETCH NEXT FROM col_cursor INTO @columnName    

   WHILE @@FETCH_STATUS = 0    
   BEGIN    
       IF @where <> '' 
           SET @where = @where + ' OR' 

       SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + '''' 
       FETCH NEXT FROM col_cursor INTO @columnName    
   END    

   CLOSE col_cursor    
   DEALLOCATE col_cursor  

   SET @sqlCommand = @sqlCommand + @where 
   PRINT @sqlCommand 
   EXEC (@sqlCommand)  
END TRY 
BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.'
   PRINT error_message()

   IF CURSOR_STATUS('variable', 'col_cursor') <> -3 
   BEGIN 
       CLOSE col_cursor    
       DEALLOCATE col_cursor  
   END 
END CATCH

- Sihusir

1

请尝试从您提供的链接中添加更多细节到您的答案。如果链接发生变化或不可用，您的答案将没有帮助。 - dckuehn

@dckuehn 感谢您的反馈，我已经添加了 SPROC。 - Sihusir

0

这听起来像是您只想知道某些数据存储在哪个表和列中，而不是在代码执行期间知道它或更改它。我之前也遇到过这个问题，以下方法解决了它：

使用phpmyadmin等工具将数据库以SQL格式下载下来，用文本编辑器打开并搜索您想要的内容。

- Sérgio

如果数据库的大小为GB级别，那么打开和搜索这样的文件将非常困难。 - Igor Micev

0

这是一个可爱的小技巧，可以减少一些复制粘贴的工作，因为可以使用查询轻松地生成命令。

将 WHERE 子句中的 IN 运算符反转为 VALUE IN <fields>（而不是更常见的用法 FIELD IN <values>）。

SELECT col_1, col_2, ... , col_n 
FROM <table>
WHERE CAST(<value> AS varchar(max)) IN 
   (
   CAST(col_1 AS varchar(max)),
   CAST(col_2 AS varchar(max)),
   ...,
   CAST(col_n AS varchar(max))
   )

由于varchar是一种相当可塑的数据类型，因此这变得非常简单（您可以使用ISNULL/NULLIF进行必要的修改），并且根据用例，可能可以用于多个搜索值。

更加健壮的解决方案，使用动态执行和PL/SQL编写一个过程来动态构建目标表的视图（通过读取例如MySQL的information_schema模式、Oracle的SYS模式等），受限于包含输入字符串硬编码为一系列“OR”连接/IN子句的where子句作为过滤条件。

- JJ Ward

0

我发现最好的答案就是从表格中选择select *，然后将其复制并粘贴到Excel中，再按下Ctrl+F。

- SUMguy

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- David Smithers · Accepted Answer

只需使用一些第三方工具。有几个是100%免费的，你可以选择任何一个，因为它们都会为你节省大量时间。 ApexSQL Search（搜索模式和数据），SSMS Toolpack（搜索模式和数据，但对于SQL Server 2012不免费），SQL Search（仅搜索数据）。

坦白地说，我真的不明白即使是非常有经验的DBA也为此编写脚本，如果他们可以使用某些免费工具来完成这项工作。