Microsoft SQL Sample Code

Find String in Any Column

You may have a need to find a string value in any column in your table. For example, you may want to find any record in a table that has the value 'vel' anywhere in any column in a specific table. This sample script will show you how to accomplish this. This script also has a wildcard parameter that allows you to specifiy if you want to use a wildcard match or an exact match.

Create PROCEDURE uspFindStringInTable @prmstringToFind VARCHAR(100), @prmschema sysname, @prmtable sysname, @prmWildChar Bit AS BEGIN TRY DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @prmschema + '].[' + @prmtable + '] WHERE ' If @prmWildChar = 1 Begin SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''%' + @prmstringToFind + '%'' OR ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @prmschema AND TABLE_NAME = @prmtable AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar') End Else Begin SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @prmstringToFind + ''' OR ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @prmschema AND TABLE_NAME = @prmtable AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar') End SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3) EXEC (@sqlCommand) PRINT @sqlCommand END TRY BEGIN CATCH PRINT 'There was an error. Check to make sure object exists.' PRINT error_message() END CATCH GO Exec uspFindStringInTable 'vel','dbo','tblCustomers', 1

If you need any help implementing this function or modifications made for your needs, please Click Here to get a free quote for your Microsoft SQL Development needs. 

Click Here to learn more about the WSI quoting process.