SQL Server SELECT，其中任何列包含“x"

2021-12-13 00:00:00 sql select sql-server-2008 sql-server

使用 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 列并返回所有结果，其中 foo 包含在 80 列中的任何一列中?

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

相关文章