How to find a string value in all the string columns of a table/view in SQL Server ?

 

Introduction

I am sure many times we all might have come across situations where we need to search/find a string value in all the string columns of a given table/view in SQL Server and return the matching rows from that table.

Unfortunately, we do not have any straight forward way to do this till date AFAIK. Hence, the below script can prove to be quite handy in this situation -

USE DBName --Replace with the DB in which the table resides

GO

--Declare variables and initialize them

DECLARE @TableSchema AS VARCHAR(50) = 'SchemaName' --Replace this with the name of Schema of the Table/View

DECLARE @TableName AS VARCHAR(50) = 'TableName' --Replace this with the name of the Table/View to search

DECLARE @SearchString AS VARCHAR(50) = 'SearchString' --Replace this with actual SearchString

DECLARE @Qry AS NVARCHAR(MAX)

DECLARE @Columns AS VARCHAR(MAX)

--Prepare the columns

SET @Columns = STUFF((

SELECT 

  '+' + CASE WHEN IS_NULLABLE = 'YES' THEN 'ISNULL(' + C.COLUMN_NAME + ','''')' ELSE C.COLUMN_NAME END

FROM 

  INFORMATION_SCHEMA.COLUMNS C

WHERE

  C.TABLE_SCHEMA = COALESCE(@TableSchema,C.TABLE_SCHEMA)

  AND C.TABLE_NAME = COALESCE(@TableName,C.TABLE_NAME)

  AND C.DATA_TYPE IN ('CHAR','NCHAR','NTEXT','NVARCHAR','TEXT','VARCHAR')

FOR XML PATH('')),1,1,'')

--Prepare the Query

SET @Qry = N' SELECT ' +

            '  * ' +

            ' FROM ' +

            @TableSchema + '.' + @TableName +

            ' WHERE  ' +

              @Columns + ' LIKE ''%' + @SearchString + '%'''  

--Execute the Query

EXEC SP_EXECUTESQL @Qry

Please note that the above script works only for the following column types - CHAR,NCHAR,NTEXT,NVARCHAR,TEXT,VARCHAR

Njoy searching….