January 13, 2017

Find a sting in SQL table

CREATE PROCEDURE dbo.FindStringInTable @stringToFind VARCHAR(100)
AS

BEGIN TRY
 
   DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [dbo].[TableName] WHERE '
          
   SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' OR '
   
   FROM INFORMATION_SCHEMA.COLUMNS
   
   WHERE TABLE_SCHEMA = 'dbo'
   
   AND TABLE_NAME = 'TableName' -- This condition can be removed.
   
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

   SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
   
   EXEC (@sqlCommand)
   
   PRINT @sqlCommand

END TRY

BEGIN CATCH
   PRINT 'Unknown Error !!'

END CATCH

 Execute FindStringInTable '[String to Search]'

No comments:

Post a Comment