SQL 如何搜索表中的所有列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7922744/
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 can I search all columns in a table?
提问by ahmadali shafiee
How can I search all columns of a table in SQL Server?
如何在 SQL Server 中搜索表的所有列？
回答by Marc B
SELECT ...
FROM yourtable
WHERE 'val' IN (field1, field2, field3, field4, ...)
if you're looking for exact full-field matches. If you're looking for substring matches, you'll have to go about it the long way:
如果您正在寻找精确的全场比赛。如果您正在寻找子字符串匹配项，则必须走很长一段路：
WHERE field1 LIKE '%val%' or field2 LIKE '%val%' etc....
回答by Reyan Chougle
You can even do it this way.. Dynamically creating the query..
你甚至可以这样做..动态创建查询..
SET NOCOUNT ON;
DECLARE @searchText NVARCHAR(100) = 'Test'
DECLARE @columnName NVARCHAR(100)
DECLARE @tableName NVARCHAR(100) = 'Accounts'
DECLARE @sql NVARCHAR(1000) = 'SELECT * FROM ' + @tableName +' WHERE '
DECLARE columns CURSOR FOR
SELECT sys.columns.name FROM sys.tables
INNER JOIN sys.columns ON sys.columns.object_id = sys.tables.object_id
WHERE sys.tables.name = @tableName
OPEN columns
FETCH NEXT FROM columns
INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + @columnName + ' LIKE ''%' + @searchText + '%'' OR '
FETCH NEXT FROM columns
INTO @columnName
END
CLOSE columns;
DEALLOCATE columns;
SET @sql = LEFT(RTRIM(@sql), LEN(@sql) - 2)
EXEC(@sql)
回答by Dylan Smith
I don't believe there is any shortcut for this, you have to specify out the list of columns you wish to search. I would argue that if you find yourself trying to do this alot, you probably could improve upon the DB design.
我不相信有任何快捷方式，您必须指定要搜索的列列表。我认为，如果您发现自己经常尝试这样做，您可能可以改进 DB 设计。
SELECT *
FROM MyTable
WHERE Col1 LIKE '%foo%' OR
Col2 LIKE '%foo%' OR
Col3 LIKE '%foo%' OR
Col4 LIKE '%foo%' OR
Col5 LIKE '%foo%' OR
Col6 LIKE '%foo%'
回答by tca1130
I can't take credit, but I found this and it works perfectly. You can add additional data types to the WHERE clause to include other types, as well as adding a schema name to the JOIN (commented below) to limit your search, if needed.
我不能相信，但我发现了这个，而且效果很好。如果需要，您可以向 WHERE 子句添加其他数据类型以包含其他类型，以及将模式名称添加到 JOIN（在下面评论）以限制您的搜索。
DECLARE @SQL VARCHAR(MAX)
DECLARE @SearchString VARCHAR(100)
SET @SQL=''
-- ------------------------------------------
-- Enter the string to be searched for here :
SET @SearchString=''
-- ------------------------------------------
SELECT @SQL = @SQL + 'SELECT CONVERT(VARCHAR(MAX),COUNT(*)) + '' matches in column ''+'''
+ C.name + '''+'' on table '' + ''' + SC.name + '.' + T.name +
''' [Matches for '''+@SearchString+''':] FROM ' +
QUOTENAME(SC.name) + '.' + QUOTENAME(T.name) + ' WHERE ' + QUOTENAME(C.name) +
' LIKE ''%' + @SearchString +
'%'' HAVING COUNT(*)>0 UNION ALL ' +CHAR(13) + CHAR(10)
FROM sys.columns C
JOIN sys.tables T ON C.object_id=T.object_id
JOIN sys.schemas SC ON SC.schema_id=T.schema_id -- AND SC.name = ''
JOIN sys.types ST ON C.user_type_id=ST.user_type_id
JOIN sys.types SYST ON ST.system_type_id=SYST.user_type_id
AND ST.system_type_id=SYST.system_type_id
WHERE SYST.name IN ('varchar','nvarchar','text','ntext','char','nchar')
ORDER BY T.name, C.name
-- Strip off the last UNION ALL
IF LEN(@SQL)>12
SELECT @SQL=LEFT(@SQL,LEN(@SQL)- 12)
EXEC(@SQL)
--PRINT @SQL
参考：https: //www.sqlmatters.com/Articles/Searching%20all%20columns%20in%20all%20tables%20in%20a%20database.aspx
回答by Benjamin Schlegel
I found a fast way after reading the answer of Jose Hernandez Naranjo using a subquery. This has the benefit that we do not have to add an extra column to the database.
在使用子查询阅读 Jose Hernandez Naranjo 的答案后，我找到了一种快速方法。这样做的好处是我们不必向数据库添加额外的列。
Let's suppose we have a table person with the columns PersonID, Firstname, Name, Cellphone, Email and Place, which should all be search and The full name including the Place should be outputet. The code would than look the following way:
假设我们有一个表person，其中包含PersonID、Firstname、Name、Cellphone、Email 和Place 列，这些列都应该是search，并且应该输出包括Place 在内的全名。代码将如下所示：
SELECT Firstname, Name, Place
FROM person NATURAL JOIN
(SELECT PersonID, CONCAT(Firstname, ' ', Name, ' ', Cellphone, ' ',
Email, ' ', Place) search_string FROM person) as all_in_one
WHERE search_string LIKE '%Hans%' AND search_string LIKE '%Muster%'
If you are using the SQL from another language like PHP, you can add the part behind the WHERE using a loop. Here an example from PHP:
如果您使用另一种语言（如 PHP）的 SQL，则可以使用循环在 WHERE 后面添加部分。这是一个来自 PHP 的例子：
$q = $_GET['q'];
$words = explode(" ", $q);
foreach($words as $word){
if(is_null($search_query)){
$search_query = "search_string LIKE '%$word%'";
}else{
$search_query .= " AND search_string LIKE '%$word%'";
}
}
(Attention: This example does not do any check of the GET Variable which could be potentially used for SQL injection.)
（注意：此示例不会对可能用于 SQL 注入的 GET 变量进行任何检查。）
回答by Jose Hernandez
I did this before in a project. The best way to search all columns in SQL would be to create a new column in the table and paste each columns text/data into it like so:
我之前在一个项目中做过这个。在 SQL 中搜索所有列的最佳方法是在表中创建一个新列并将每列文本/数据粘贴到其中，如下所示：
table:
|---------------------------------------------------|
| column1 | column2 | search_column |
|---------------------------------------------------|
| fooxxxxx | barxxxx | fooxxxxx barxxxx |
----------------------------------------------------|
表：
|----------------------------------------------- ----|
| 第 1 列 | 第 2 列 | search_column |
|------------------------------------------------- --|
| xxxxxx | barxxxx | fooxxxxx barxxxx |
-------------------------------------------------- --|
SELECT search_column FROM table1 LIKE %key word%
SELECT search_column FROM table1 LIKE %key word%
It works and gets the job done without writing lots of SQL code. It is also much faster to run this type of query. The only drawback is that when the table is updated and created the search_column has to be constructed.
它可以工作并完成工作，而无需编写大量 SQL 代码。运行这种类型的查询也快得多。唯一的缺点是当更新和创建表时，必须构造 search_column。
