Breaking News: Grepper is joining You.com. Read the official announcement!

SQL Server SELECT where any column contains 'x'

corneille M answered on July 21, 2022 Popularity 5/10 Helpfulness 1/10

answer SQL Server SELECT where any column contains 'x'

SQL Server SELECT where any column contains 'x'

Comment

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

Popularity 5/10 Helpfulness 1/10 Language sql

Source: stackoverflow.com

Tags: any contains select sql sql-server where

Link to this answer
Share Copy Link

Contributed on Jul 21 2022

corneille M

0 Answers Avg Quality 2/10

SQL Server SELECT where any column contains 'x'

Contents

More Related Answers

SQL Server SELECT where any column contains 'x'

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.