SQL 在表的所有列中搜索值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6539873/
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 all columns of a table for a value?
提问by Jesse
I've looked for an answer to this, but all I can find is people asking how to search all columns of ALL tables in a database for a value. I just want to search all columns for a specific table. The code people have come up with for the all tables question is complicated and hard for me to figure out where exactly it's searching a specific table. Can somebody help me out? Thanks
我一直在寻找答案，但我能找到的只是人们询问如何在数据库中搜索所有表的所有列以获取值。我只想搜索特定表的所有列。人们为所有表格问题提出的代码很复杂，我很难弄清楚它究竟在哪里搜索特定表格。有人可以帮我吗？谢谢
回答by David Smithers
Just use some third party tool. There are several that are 100% free and you can't go wrong with any of these because they will save you a ton of time.
只需使用一些第三方工具。有几个是 100% 免费的，其中任何一个都不会出错，因为它们会为您节省大量时间。
ApexSQL Search(searches both schema and data), SSMS Toolpack(searches schema and data but not free for SQL Server 2012), SQL Search(searches data only).
ApexSQL 搜索（搜索架构和数据）、SSMS 工具包（搜索架构和数据，但 SQL Server 2012 不是免费的）、SQL 搜索（仅搜索数据）。
Frankly, I don't really understand why even very experienced DBAs bother writing scripts for this if they can use some tool for free that will do the job.
坦率地说，我真的不明白为什么即使是非常有经验的 DBA 也不愿意为此编写脚本，如果他们可以免费使用一些可以完成这项工作的工具。
回答by Bill Karwin
I have no idea of the column types or data values you're searching for, but I'd guess you're trying to search for a substring among multiple text columns.
我不知道您要搜索的列类型或数据值，但我猜您正在尝试在多个文本列中搜索子字符串。
This is a job for Full-Text Search.
这是全文搜索的工作。
Don't waste time with LIKE '%' + @SearchStr + '%'. You have to write a lot of complicated code to support it, and that solution won't perform well anyway.
不要浪费时间LIKE '%' + @SearchStr + '%'。您必须编写大量复杂的代码来支持它，而该解决方案无论如何都不会很好地执行。
回答by a_horse_with_no_name
In a similar questionI mentioned SQL Workbench/J.
在一个类似的问题中，我提到了 SQL Workbench/J。
The command that searches the database can also be limited to just one table. So even if that question was PostgreSQL specific, the tool works for SQL Server as well as far as I know.
搜索数据库的命令也可以仅限于一张表。因此，即使该问题是特定于 PostgreSQL 的，据我所知，该工具也适用于 SQL Server。
回答by Aaron Bertrand
Here is a solution that, like @Decker97's approach, figures out from metadata which columns are eligible for text search. Assumes 2005+ for use of XML PATH as well as sys.columns, sys.tables, etc. Supports TEXT/NTEXT/CHAR/NCHAR/VARCHAR/NVARCHAR, and even puts the leading N on the search string where appropriate. Does not support XML columns. What it does do slightly differently is that it returns a single resultset for each table, not for every single column, so you only get one row per actual table row even if multiple columns match. If the goal is to understand how it works rather than simply having a solution, it will probably take a bit more than this... perhaps I should blog about this problem (I should probably not be lazy and actually build the column lists instead of just using SELECT *).
这是一个解决方案，就像@Decker97 的方法一样，它从元数据中找出哪些列有资格进行文本搜索。假设 2005+ 使用 XML PATH 以及 sys.columns、sys.tables 等。支持 TEXT/NTEXT/CHAR/NCHAR/VARCHAR/NVARCHAR，甚至在适当的地方将前导 N 放在搜索字符串上。不支持 XML 列。它所做的略有不同的是，它为每个表返回一个结果集，而不是为每一列返回一个结果集，因此即使多列匹配，每个实际表行也只能获得一行。如果目标是了解它是如何工作的而不是简单地找到一个解决方案，那么它可能需要更多的时间......也许我应该写博客关于这个问题（我可能不应该偷懒并实际构建列列表而不是只需使用 SELECT *)。
DECLARE @SearchTerm NVARCHAR(32) = 'foo';
DECLARE @TableName NVARCHAR(128) = NULL;
SET NOCOUNT ON;
DECLARE @s NVARCHAR(MAX) = '';
WITH [tables] AS
(
SELECT [object_id]
FROM sys.tables AS t
WHERE (name = @TableName OR @TableName IS NULL)
AND EXISTS
(
SELECT 1
FROM sys.columns
WHERE [object_id] = t.[object_id]
AND system_type_id IN (35,99,167,175,231,239)
)
)
SELECT @s = @s + 'SELECT '''
+ REPLACE(QUOTENAME(OBJECT_SCHEMA_NAME([object_id])),'''','''''')
+ '.' + REPLACE(QUOTENAME(OBJECT_NAME([object_id])), '''','''''')
+ ''',* FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
+ '.' + QUOTENAME(OBJECT_NAME([object_id])) + ' WHERE ' +
(
SELECT QUOTENAME(name) + ' LIKE ' + CASE
WHEN system_type_id IN (99,231,239)
THEN 'N' ELSE '' END
+ '''%' + @SearchTerm + '%'' OR '
FROM sys.columns
WHERE [object_id] = [tables].[object_id]
AND system_type_id IN (35,99,167,175,231,239)
ORDER BY name
FOR XML PATH(''), TYPE
).value('.[1]', 'nvarchar(max)') + CHAR(13) + CHAR(10)
FROM [tables];
SELECT @s = REPLACE(@s,' OR ' + CHAR(13),';' + CHAR(13));
/*
make sure you use Results to Text and adjust Tools / Options /
Query Results / SQL Server / Results to Text / Maximum number
of characters if you want a chance at trusting this output
(the number of tables/columns will certainly have the ability
to exceed the output limitation)
*/
SELECT @s;
-- EXEC sp_executeSQL @s;
回答by Decker97
I modified this stored proc to take a table name as the second parameter and just search that table for the data:
我修改了这个存储过程以将表名作为第二个参数，然后在该表中搜索数据：
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
回答by JJ Ward
Cutesie little work-around that involves a bit less copy-paste, since the command can be produced easily using queries.
可爱的小解决方法涉及较少的复制粘贴，因为可以使用查询轻松生成命令。
Invert the INoperator in a WHEREclause as VALUE IN <fields>(as opposed to the more common use case of FIELD IN <values>).
反转子句中的IN运算符WHEREas 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))
)
Since varchar is a pretty malleable data type, this becomes pretty foolproof (you can throw ISNULL/NULLIFto modify as needed), and depending on the use case can probably be used across more than one search value.
由于 varchar 是一种非常具有延展性的数据类型，这变得非常万无一失（您可以根据需要抛出ISNULL/NULLIF进行修改），并且根据用例，可能可以跨多个搜索值使用。
A more robustsolution, using dynamic execution and PL/SQL would be to write a procedure to dynamically build a view of the target table (via reading e.g. MySQL's information_schema schema, Oracle's SYS schema, etc.), constrained to a where clause containing the input string hard-coded into a series of 'OR'-concatenated/IN clauses for filter conditions.
使用动态执行和 PL/SQL的更健壮的解决方案是编写一个过程来动态构建目标表的视图（通过读取例如 MySQL 的 information_schema 模式、Oracle 的 SYS 模式等），约束到包含输入字符串硬编码为一系列用于过滤条件的“OR”连接/IN 子句。
回答by Sihusir
I have come across this issue, normally after uploading data from a CSV file where I had to modify the commas ',' in text fields so the data would load properly & once in SQL Server, the need comes to change the modified character back to a comma & it's helpful to be able to search the entire table. Greg Robidoux at mssqltips has posted a Stored Procedure that does just this, searches the Columns of a specified Table for a particular String value. You can find it along with a SPROC that does not use the cursor & more details here:
我遇到过这个问题，通常在从 CSV 文件上传数据后，我必须修改文本字段中的逗号“，”，以便数据正确加载，并且一旦在 SQL Server 中，需要将修改后的字符更改回逗号 & 能够搜索整个表格很有帮助。mssqltips 的 Greg Robidoux 发布了一个存储过程，它就是这样做的，在指定表的列中搜索特定的字符串值。您可以在此处找到它以及不使用光标的 SPROC 和更多详细信息：
I have posted the original SPROC below:
我在下面发布了原始 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
回答by aGuy
I've found the best answer is just to select *from the table and then copy & paste into Excel and hit Ctrl+F
我发现最好的答案是select *从表格中复制并粘贴到 Excel 中，然后按 Ctrl+F
回答by Sérgio
This sounds like you just want to know which table and column some data is stored, not that you want to know that during the execution of your code, or change it. I also had this problem and this solved it:
这听起来像是您只想知道某些数据存储在哪个表和列，而不是您想在代码执行期间知道或更改它。我也有这个问题，这解决了它：
Download your database in SQL format (using phpmyadmin, for example), open it with a text editor and search for the occurrences you want.
以 SQL 格式下载您的数据库（例如，使用 phpmyadmin），使用文本编辑器打开它并搜索您想要的事件。
