By: Greg Robidoux | Last Updated: 2016-01-31 | Comments (74) | Scripts
Sometimes there is a need to find if a string value exists in any column in your table. Although there are system stored procedures that do a "for each database" or a "for each table", there is not a system stored procedure that does a "for each column". So trying to find a value in any column in your database requires you to build the query to look through each column you want to search using an OR operator between each column. Is there any way this can be dynamically generated?
Once again this is where T-SQL comes in handy along with the use of system tables or system views. The code below allows you to search for a value in all text data type columns such as (char, nchar, ntext, nvarchar, text and varchar).
The stored procedure gets created in the master database so you can use it in any of your databases and it takes three parameters:
- stringToFind - this is the string you are looking for. This could be a simple value as 'test' or you can also use the % wildcard such as '%test%', '%test' or 'test%'.
- schema - this is the schema owner of the object
- table - this is the table name you want to search, the procedure will search all char, nchar, ntext, nvarchar, text and varchar columns in the table
Option 1 - Uses a Cursor (original method)
The first thing you need to do is create this stored procedure by copying the below code and executing it in a query window.
USE master GO CREATE 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.' PRINT error_message() IF CURSOR_STATUS('variable', 'col_cursor') <> -3 BEGIN CLOSE col_cursor DEALLOCATE col_cursor END END CATCH
Option 2 - Does Not Use a Cursor (new method)
This is a newer and simpler approach and elminates the need for a cursor.
We recently ran into an issue that I worked on with Russ Mittler where the database setting had cursors set to local, so the script above did not work because it needed that setting to be global. This database setting can be checked using the below code:
SELECT is_local_cursor_default FROM sys.databases WHERE name = DB_NAME()
So based on that issue, I wrote a different version of this stored procedure that does not use cursors.
USE master GO CREATE PROCEDURE dbo.sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname AS BEGIN TRY DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE ' SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' OR ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table 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 'There was an error. Check to make sure object exists.' PRINT error_message() END CATCH
After the stored procedure has been created it needs to be marked as a system stored procedure.
USE master GO EXEC sys.sp_MS_marksystemobject sp_FindStringInTable GO
Once the stored procedure has been created you can run some tests.
Here are some tests that were done against the AdventureWorks database.
Find the value 'Irv%' in the Person.Address table.
EXEC sp_FindStringInTable 'Irv%', 'Person', 'Address'
Find the value '%land%' in the Person.Address table.
EXEC sp_FindStringInTable '%land%', 'Person', 'Address'
Find the value '%land%' in the Person.Contact table.
EXEC sp_FindStringInTable '%land%', 'Person', 'Contact'
That's all there is to it. Once this has been created you can use this against any table and any database on your server.
- Add this to your scripts toolbox in case you have the need to search through a table to find a certain value.
- Take a look at this tip that allows you search across all tables in your database.
- Thanks to Paul for the idea for this tip
- One thing to keep in mind is that if you are using the % in front of the value such as '%land' this will force SQL Server to scan the table and not use the indexes. Also, since you are searching through all columns using an OR clause it is a pretty good bet that you will do a table scan, so be aware of this on large tables.
Last Updated: 2016-01-31
About the author
View all my tips
- Find text strings in character data types using SQ...
- Finding and listing all columns in a SQL Server da...
- Searching and finding a string value in all column...
- Scan a SQL Server Database for Objects and Columns...
- SQL Server Find and Replace Values in All Tables a...
- List columns and attributes for every table in a S...
- Search all string columns in all SQL Server databa...
- More Database Developer Tips...