SQL 在不指定所有列名的情况下对所有列应用 like ？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8815025/
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
Apply like over all columns without specifying all column names?
提问by Michael A
I've found myself in a position where I'm working an unfamiliar database that has a vast number of columns to each table. I have an idea of what data I'm looking for but I don't know what column it resides in and need to use likein order to locate the exact data that I need (and have to repeat this task for multiple sets of data).
我发现自己在一个陌生的数据库中工作，每个表都有大量的列。我知道我在寻找什么数据，但我不知道它驻留在哪个列中，需要使用like它来定位我需要的确切数据（并且必须为多组数据重复此任务） .
Is there a way to apply like over a cartesian select?
有没有办法像笛卡尔选择一样应用？
The following should explain what I'd like to do a bit better (even though it's syntactically ridiculous):
下面应该解释我想做得更好一点（即使它在语法上很荒谬）：
select
*
from
a_table
where
* like '%x%'
edit:
编辑：
Note that I'm not intending on using a cartesion select in any reports - it's purposes here would be to help me to identify the relevant columns that I would need to put into my queries and to help me gain familiarity with the database.
请注意，我不打算在任何报告中使用 cartesion select - 这里的目的是帮助我确定需要放入查询中的相关列并帮助我熟悉数据库。
采纳答案by Oleg Dok
Generally - its not possible in reasonable way (without digging in DB metadata), but if you know the names of columns, you may use trick like this:
通常 - 它不可能以合理的方式（不挖掘数据库元数据），但如果你知道列的名称，你可以使用这样的技巧：
select
YourTable.*
FROM YourTable
JOIN
(
select
id,
ISNULL(column1,'')+ISNULL(Column2,'')+...+ISNULL(ColumnN,'') concatenated
FROM YourTable
) T ON T.Id = YourTable.Id
where t.concatenated like '%x%'
OR
或者
if you search for words - use the FTS capabilities, because the upper query is a performance killer
如果您搜索单词 - 使用 FTS 功能，因为上查询是性能杀手
回答by Animesh
There is a similar discussion here.
有一个类似的讨论在这里。
There is no direct way and you have to do it in this fashion:
没有直接的方法，您必须以这种方式进行：
SELECT Name, Age, Description, Field1, Field2
FROM MyTable
WHERE Name LIKE 'Something%' OR Description LIKE 'Something%' OR Field1 LIKE 'Something%' OR Field2 LIKE 'Something%'
One of the solutions posted in that forum was this, This uses dynamic SQL:
该论坛中发布的解决方案之一是，这使用动态 SQL：
CREATE PROCEDURE TABLEVIEWSEARCH @TABLENAME VARCHAR(60),@SEARCHSTRING VARCHAR(50)
-- EXEC TABLEVIEWSEARCH 'GMACT','demo'
-- EXEC TABLEVIEWSEARCH 'TABLEORVIEW','TEST'
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(500),
@COLUMNNAME VARCHAR(60)
CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))
SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #TMPCOLLECTION
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.NAME = @TABLENAME
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY TBLNAME,COLNAME
DECLARE C1 CURSOR FOR
SELECT COLNAME FROM #TMPCOLLECTION ORDER BY COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
--SET @SQL = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''
SET @SQL = 'IF EXISTS(SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''%' + @SEARCHSTRING + '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''''%' + @SEARCHSTRING + '%'''''') ;'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS
GO
CREATE PROCEDURE TABLEVIEWSEARCH2 @TABLENAME VARCHAR(60),@SEARCHSTRING VARCHAR(50)
-- EXEC TABLEVIEWSEARCH2 'GMACT','SOURCE'
-- EXEC TABLEVIEWSEARCH2 'TABLEORVIEW','TEST'
AS
BEGIN
SET NOCOUNT ON
DECLARE @FINALSQL VARCHAR(MAX),
@COLUMNNAMES VARCHAR(MAX)
SET @FINALSQL = 'SELECT * FROM [' + @TABLENAME + '] WHERE 1 = 2 '
SELECT
@FINALSQL = @FINALSQL + ' OR [' + SYSCOLUMNS.NAME + '] LIKE ''%' + @SEARCHSTRING + '%'' '
FROM SYSCOLUMNS
WHERE OBJECT_NAME(id) = @TABLENAME
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY COLID
PRINT @FINALSQL
EXEC(@FINALSQL)
END --PROC
I have tested this on an employee table containing the following data:
我在包含以下数据的员工表上对此进行了测试：
Running the following statement
运行以下语句
EXEC TABLEVIEWSEARCH2 'employee','2'
EXEC TABLEVIEWSEARCH2 'employee','2'
resulted in:
导致：
2 1 eng2
4 2 dev2
7 3 sup2
9 4 qa2
I thought I would provide some more example of this in action, since the Emp table above has only one field where it was searching the data.
我想我会提供更多这样的例子，因为上面的 Emp 表只有一个用于搜索数据的字段。
This is a task table from a todo database:
这是一个来自 todo 数据库的任务表：
Searching for the phrase en: (highlighted cells where data matched)
搜索短语en：（突出显示数据匹配的单元格）
EXEC TABLEVIEWSEARCH2 'task','en'
EXEC TABLEVIEWSEARCH2 'task','en'
回答by Walter Heck
No, this is not possible with SQL. It would be considered bad practice as well, although I can see the use case in your scenario. Your best bet is to script it in your favorite language by retrieving a list of all column names and then executing either a separate query with a like for each column or a single large query that combines it all:
不，这在 SQL 中是不可能的。这也被认为是不好的做法，尽管我可以在您的场景中看到用例。最好的办法是通过检索所有列名的列表，然后为每列执行一个单独的查询或将所有列名组合在一起的单个大查询，以您喜欢的语言编写脚本：
select
*
from
a
where
a.column_1 like '%blah%' or
a.column_2 like '%blah%';
or, separate queries:
或者，单独的查询：
select
*
from
a
where
a.column_1 like '%blah%'
select
*
from
a
where
a.column_2 like '%blah%'
回答by Mikael Eriksson
You can try something like this but if your table is really big you might have some trouble because it will create an XML of your entire table and then query the XML for the search string. The output is the column name(s) where the string is found.
您可以尝试这样的操作，但如果您的表真的很大，您可能会遇到一些问题，因为它会创建整个表的 XML，然后查询 XML 以获取搜索字符串。输出是找到字符串的列名。
;with C(TableXML) as
(
select *
from YourTable
for xml path('T'), type
)
select distinct T.X.value('local-name(.)', 'sysname') as ColumnName
from C
cross apply C.TableXML.nodes('/T/*') as T(X)
where T.X.value('.', 'varchar(max)') like '%x%'
回答by tristan3fish
Thanks Nanda :)
谢谢南达:)
here is my slimmed down script:
这是我的精简脚本：
use a_database
declare
@TableName as nvarchar(50) = 'a_table',
@FilterContition as nvarchar(50) = 'like ''%x%''',
@ColumnName as nvarchar(100),
@ColumnCursor as cursor,
@Sql as nvarchar(4000)
set @ColumnCursor = cursor for
select distinct c.name
from sys.objects as o
inner join sys.columns as c
on o.object_id = c.object_id
where o.name = @TableName
and type_name(c.user_type_id) in ('VARCHAR','NVARCHAR','CHAR','NCHAR')
open @ColumnCursor
fetch next from @ColumnCursor into @ColumnName
set @Sql = 'select * from ' + @TableName + ' where ' + @ColumnName + ' ' + @FilterContition
while @@fetch_status = 0
begin
fetch next from @ColumnCursor into @ColumnName
set @Sql = @Sql + ' and ' + @ColumnName + ' ' + @FilterContition
end
close @ColumnCursor
deallocate @ColumnCursor
exec(@Sql)
it uses: - dynamic sql - a cursor - database metadata
它使用： - 动态 sql - 游标 - 数据库元数据
回答by asalam345
Create PROCEDURE dbo.sp_FindStringInTable @stringToFind VARCHAR(100), @table sysname
AS
BEGIN TRY
DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @table + '] WHERE '
SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' OR '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table
AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')
SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
EXEC (@sqlCommand)
PRINT @sqlCommand
END TRY
BEGIN CATCH
PRINT 'There was an error. Check to make sure object exists.'
PRINT error_message()
END CATCH
--then call by this
--然后通过这个调用
EXEC sp_FindStringInTable 'yoursearchitem', 'tablename'
回答by Bigtick
one easy way: 1. select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'your_table'
一种简单的方法：1. 从 INFORMATION_SCHEMA.COLUMNS 中选择 COLUMN_NAME，其中 TABLE_NAME = 'your_table'
- Copy Paste the result into an Excel sheet Cell B1
- type isnull( in Cell A1
- type ,'')+ in Cell C1
- type =A1&B1&C1 in Cell D1
- Drag Cell A1 and C1 and D1 down
Copy Paste Column D into SQL
Add select * from your_table where ( at the beginning
- Delete the + at the end
- Add ) like '%x%' at the end
- execute
- 将结果复制粘贴到 Excel 工作表 Cell B1
- 输入 isnull( 在单元格 A1 中
- 在单元格 C1 中键入 ,'')+
- 在单元格 D1 中输入 =A1&B1&C1
- 将单元格 A1 和 C1 和 D1 向下拖动
将列 D 复制粘贴到 SQL
添加 select * from your_table where ( 在开头
- 删除末尾的+
- 在末尾添加 ) like '%x%'
- 执行
Excel is your friend!
Excel 是您的朋友！
