Hello Everyone. In this post, we want to discuss about a nice function to search string in whole table (all row/column) in SQL Server. Before we get started, if you want to know about using of Cursor, please go through the following article: Using Cursor in SQL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
USE [NiloyHero1920] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- sp_FindStringInTable '%*%','dbo','AModelApplicability' ALTER PROCEDURE [dbo].[sp_FindStringInTable] @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. Check to make sure object exists.' IF CURSOR_STATUS('variable', 'col_cursor') <> -3 BEGIN CLOSE col_cursor DEALLOCATE col_cursor END END CATCH |
Search string in whole table
The article was published on September 11, 2014 @ 10:51 AM
Leave a Comment