SQL Server SELECT，其中任何列包含"x" [英] SQL Server SELECT where any column contains 'x'

问题描述

使用SQL Server 2008，我有一个名为testing的表，该表有80列，我想找到一个名为foo的值.

Using SQL Server 2008, say I have a table called testing with 80 columns and I want to find a value called foo.

我可以做到:

SELECT * 
FROM testing 
WHERE COLNAME = 'foo'

是否可以查询所有80列并返回80列中任何一个包含foo的所有结果?

Is it possible I can query all 80 columns and return all the results where foo is contained in any of the 80 columns?

谢谢.

推荐答案

第一种方法(已测试) 首先获取用逗号分隔的字符串变量中的列的列表，然后您可以通过使用IN

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

检查存储过程，该存储过程在下面首先获取列，然后搜索字符串:

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 );

第二种方法 在sql server中，您可以获取表的对象ID，然后使用该对象ID可以获取列.在这种情况下，将如下所示:

2nd Method In 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:

步骤1:首先获取表格的对象ID

Step 1: First get Object Id of table

select * from sys.tables order by name    

第2步:现在获取表中的列并在其中搜索:

Step 2: Now get columns of your table and search in it:

 select * from testing where 'foo' in (select name from sys.columns  where  object_id =1977058079)

注意:object_id是第一步为相关表获取的内容

Note: object_id is what you get fetch in first step for you relevant table

这篇关于SQL Server SELECT，其中任何列包含"x"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！