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