About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices

Friday 30 September 2011

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….