SQL Server SELECT，其中任何列包含“x”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28717868/
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
SQL Server SELECT where any column contains 'x'
提问by nsilva
Using SQL Server 2008, say I have a table called testingwith 80 columns and I want to find a value called foo.
使用 SQL Server 2008，假设我有一个名为testing80 列的表，我想找到一个名为foo.
I can do:
我可以：
SELECT *
FROM testing
WHERE COLNAME = 'foo'
Is it possible I can query all 80 columns and return all the results where foois contained in any of the 80 columns?
我是否可以查询所有 80 列并返回foo包含在任何 80 列中的所有结果？
Thanks in advance.
提前致谢。
采纳答案by ubaid ashraf
First Method(Tested)First get list of columns in string variable separated by commas and then you can search 'foo' using that variable by use of IN
第一种方法（已测试）首先获取以逗号分隔的字符串变量中的列列表，然后您可以使用该变量搜索“foo”IN
Check stored procedure below which first gets columns and then searches for string:
检查下面的存储过程，它首先获取列然后搜索字符串：
DECLARE @TABLE_NAME VARCHAR(128)
DECLARE @SCHEMA_NAME VARCHAR(128)
-----------------------------------------------------------------------
-- Set up the name of the table here :
SET @TABLE_NAME = 'testing'
-- Set up the name of the schema here, or just leave set to 'dbo' :
SET @SCHEMA_NAME = 'dbo'
-----------------------------------------------------------------------
DECLARE @vvc_ColumnName VARCHAR(128)
DECLARE @vvc_ColumnList VARCHAR(MAX)
IF @SCHEMA_NAME =''
BEGIN
PRINT 'Error : No schema defined!'
RETURN
END
IF NOT EXISTS (SELECT * FROM sys.tables T JOIN sys.schemas S
ON T.schema_id=S.schema_id
WHERE T.Name=@TABLE_NAME AND S.name=@SCHEMA_NAME)
BEGIN
PRINT 'Error : The table '''+@TABLE_NAME+''' in schema '''+
@SCHEMA_NAME+''' does not exist in this database!'
RETURN
END
DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT CASE WHEN PATINDEX('% %',C.name) > 0
THEN '['+ C.name +']'
ELSE C.name
END
FROM sys.columns C
JOIN sys.tables T
ON C.object_id = T.object_id
JOIN sys.schemas S
ON S.schema_id = T.schema_id
WHERE T.name = @TABLE_NAME
AND S.name = @SCHEMA_NAME
ORDER BY column_id
SET @vvc_ColumnList=''
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @vvc_ColumnName
WHILE @@FETCH_STATUS=0
BEGIN
SET @vvc_ColumnList = @vvc_ColumnList + @vvc_ColumnName
-- get the details of the next column
FETCH NEXT FROM TableCursor INTO @vvc_ColumnName
-- add a comma if we are not at the end of the row
IF @@FETCH_STATUS=0
SET @vvc_ColumnList = @vvc_ColumnList + ','
END
CLOSE TableCursor
DEALLOCATE TableCursor
-- Now search for `foo`
SELECT *
FROM testing
WHERE 'foo' in (@vvc_ColumnList );
2nd MethodIn sql server you can get object id of table then using that object id you can fetch columns. In that case it will be as below:
第二种方法在 sql server 中，您可以获取表的对象 id，然后使用该对象 id 可以获取列。在这种情况下，它将如下所示：
Step 1:First get Object Id of table
步骤 1：首先获取表的对象 ID
select * from sys.tables order by name
Step 2:Now get columns of your table and search in it:
第 2 步：现在获取表格的列并在其中搜索：
select * from testing where 'foo' in (select name from sys.columns where object_id =1977058079)
Note: object_id is what you get fetch in first step for you relevant table
注意：object_id 是您在第一步中为相关表获取的内容
回答by Gordon Linoff
You can use in:
您可以使用in：
SELECT *
FROM testing
WHERE 'foo' in (col1, col2, col3, . . . );
回答by yaritaft
I think this is one of the best ways of doing it
我认为这是最好的方法之一
SELECT * FROM sys.columns a
inner join
(
SELECT object_id
FROM sys.tables
where
type='U'--user table
and name like 'testing'
) b on a.object_id=b.object_id
WHERE a.name like '%foo%'
回答by Adil
You can use in and you can get the column names dynamically and pass them to INclause by making sql string and executing it using execute sp_executesql.
您可以使用 in 并且可以动态获取列名并将它们传递给IN子句，方法是制作 sql 字符串并使用execute sp_executesql.
declare @sql nvarchar(2100)
declare @cols nvarchar(2000)
declare @toSearch nvarchar(200)
declare @tableName nvarchar(200)
set @tableName = 'tbltemp'
set @toSearch = '5'
set @cols =(
SELECT LEFT(column_name, LEN(column_name) - 1)
FROM (
SELECT column_name + ', '
FROM INFORMATION_SCHEMA.COLUMNS where table_name = @tableName
FOR XML PATH ('')
) c (column_name )
)
set @sql = 'select * from tbltemp where '''+ @toSearch + ''' in (' + @cols + ')';
execute sp_executesql @sql
回答by BornToCode
I took the idea from ubaid ashraf's answer, but made it actually work. Just change MyTableName here:
我从ubaid ashraf 的回答中得到了这个想法，但让它真正起作用。只需在此处更改 MyTableName：
SELECT STUFF((
SELECT ', ' + c.name
FROM sys.columns c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name != 'int' AND t.name != 'bit' AND t.name !='date' AND t.name !='datetime'
AND object_id =(SELECT object_id FROM sys.tables WHERE name='MyTableName')
FOR XML PATH('')),1,2,'')
You could tweak it to your needs and add or remove conditions from the where column (the 't.name != 'int' AND t.name != 'bit' etc. part), e.g. add 't.name != 'uniqueidentifier'' to avoid getting Conversion failed when converting the varchar value 'myvalue' to data type inttype of errors..
您可以根据需要调整它并从 where 列（'t.name !='int' AND t.name !='bit' 等部分）添加或删除条件，例如添加 't.name != ' uniqueidentifier'' 以避免Conversion failed when converting the varchar value 'myvalue' to data type int出现错误类型..
Then copy pastethe result into this query (otherwise it didn't work):
然后将结果复制粘贴到此查询中（否则它不起作用）：
SELECT * from MyTableName where 'foo' in (COPY PASTE PREVIOUS QUERY RESULT INTO HERE)
