Finding string across the table - SQL Server

Pandians
Posted by Pandians under Sql Server category on | Views : 2724
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

Comments or Responses

Posted by: Sarvesh on: 8/26/2009 Level:Starter | Status: [Member]
hi pandian

its very useful.

Thanks
Sarvesh

Login to post response