Apply in:
Microsoft SQL Server 2008 R2
1.
Query:
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
CREATE PROCEDURE sp_FindStringInTable_Test
@stringToFind VARCHAR(100),
@schema sysname,
@table sysname
AS
DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @cursor VARCHAR(8000)
BEGIN TRY
SET @sqlCommand = 'SELECT * FROM ' + @schema + '.' + @table + ' WHERE'
SET @where = ''
SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''' + @schema + '''
AND TABLE_NAME = ''' + @table + '''
AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'
EXEC (@cursor)
OPEN col_cursor
FETCH NEXT FROM col_cursor INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @where <> ''
SET @where = @where + ' OR'
SET @where = @where + ' ' + @columnName + ' LIKE ''' + @stringToFind + ''''
FETCH NEXT FROM col_cursor INTO @columnName
END
CLOSE col_cursor
DEALLOCATE col_cursor
SET @sqlCommand = @sqlCommand + @where
--PRINT @sqlCommand
EXEC (@sqlCommand)
END TRY
BEGIN CATCH
PRINT 'There was an error'
IF CURSOR_STATUS('variable', 'col_cursor') <> -3
BEGIN
CLOSE col_cursor
DEALLOCATE col_cursor
END
END CATCH
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
2.
Try to run the procedure,
Example: declare a specific of table, dummy table = "Sample_Table_1"
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
EXEC sp_FindStringInTable_Test '%JAKARTA UTARA%', 'DBO', 'Sample_Table_1'
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
Result Messages:
3.
To remove the procedure,
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
DROP PROCEDURE sp_FindStringInTable_Test
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
Source:
http://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/