Example and Notes
Use this SQL Server example as a starting point for development, troubleshooting, reporting, or maintenance work. Review it against your schema, data volume, permissions, and SQL Server version before using it in production.
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
Production Review
WSI can adapt this script for your database, improve error handling, tune performance, document the logic, and help deploy it safely.