Finding string across the table - SQL Server

Finding string across the table - SQL Server

Scenario: I just want to find a string like Setaceo, But I don't know in which column i have to find.

Solution: The following script is used to find the string given(@FindWhat) in the all string related columns in given table(@Table_Name).

1.Declaring Variables Required :

DECLARE   @Table_Name	VARCHAR(MAX),
	@FindWhere	VARCHAR(MAX),
	@FindWhat	VARCHAR(MAX),	
	@SQLs		NVARCHAR(MAX)

2. Initializing Data :

SELECT	@Table_Name	=	'<TABLE_NAME>'
SELECT	@FindWhat	=	'<FIND_WHAT>'

3. Script for finding the string in the table given:

;WITH CTEs
AS
(
SELECT C.[NAME] 'ColumnName' ,T.[NAME] 'ColumnType' FROM SYS.SYSCOLUMNS C JOIN SYS.SYSTYPES T
	ON	(C.xType	=	T.xType)
	WHERE C.ID = OBJECT_ID(@Table_Name)
	AND	  T.CollationID IS NOT NULL
)

SELECT @FindWhere = COALESCE(@FindWhere + ' Like ''%' + @FindWhat + '%'' Or ','') + ColumnName FROM CTEs
SELECT @SQLs = N'SELECT * FROM ' + @Table_Name + N' WHERE ' + @FindWhere + N' Like ''%' + @FindWhat + N'%'''
EXEC SP_EXECUTESQL @SQLs

Cheers