Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Searching and finding a string value in all columns in a SQL Server table

MSSQLTips author Greg Robidoux By:   |   Read Comments (26)   |   Related Tips: More > Scripts
Problem

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?

Solution

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

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 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

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.

USE AdventureWorks
GO
EXEC sp_FindStringInTable 'Irv%''Person''Address'

Find the value '%land%' in the Person.Address table.

USE AdventureWorks
GO
EXEC sp_FindStringInTable '%land%''Person''Address'

Find the value '%land%' in the Person.Contact table.

USE AdventureWorks
GO
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.

Next Steps
  • 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 Update: 6/18/2008


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, August 18, 2014 - 10:04:03 PM - Cesar Read The Tip

Hi, as I can load the result in a datagrid in c # and entity framework 

      using (DatEntities context = new DatEntities());

 

      dataGridViewClients.DataSource  =  context.sp_FindStringInTable ("%" + (textBoxName.Text), "dbo",     "Clients"); 

thanks for the help.


Wednesday, July 24, 2013 - 7:33:09 AM - Hitesh Read The Tip

hehe - @Rajesh

 

Please check datatypes. you may get idea by yourself.

 

Thanks


Wednesday, July 17, 2013 - 9:11:32 AM - Rajesh Read The Tip

Its only for string value what about date field then?


Thursday, April 25, 2013 - 11:15:54 AM - suprduprman Read The Tip

Hello! 

I tried to create a stored procedure in heidi and using phpmyadmin and got erors trying to copy and paste this code. Where do you copy this code in order to make it work and do you have to add parameters yourself ? I just tried to copy it in the ROUTINE BODY in heidi but it shows this eror: USE IS NOT ALLOWED IN STORED PROCEDURES.

 

i copied this code: 

USE master 
GO 

CREATE PROCEDURE 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') <> -
   
BEGIN 
       CLOSE 
col_cursor    
       
DEALLOCATE col_cursor  
   
END 
END 
CATCH

 

 

 


Friday, April 12, 2013 - 9:46:44 AM - Reto Egeter (FullParam) Read The Tip

Looks great. I did a similar (non-sproc) script as well posted here: http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/

Since i've added the @FullRowResult and the @SearchStrTableName it looks like we are overall doing the same thing.


Thursday, January 17, 2013 - 1:25:16 PM - Kevin Read The Tip

 

@Greg - THANKS...That worked!


Wednesday, January 16, 2013 - 5:37:52 PM - Greg Robidoux Read The Tip

@Kevin - I just changed the code to handle this.  Can you try to recreate the function.  I added [  ] around the column names, schema and tablename.


Wednesday, January 16, 2013 - 4:59:46 PM - Kevin Read The Tip

 

@Greg - the error is...

 

156    15    1    NULL    1    Incorrect syntax near the keyword 'GROUP'.


Wednesday, January 16, 2013 - 11:15:31 AM - Greg Robidoux Read The Tip

@Kevin - try to add this code to the CATCH section.  You can add this right after the BEGIN CATCH and before the PRINT 'There was an error"

 

SELECT ERROR_NUMBER() ,ERROR_SEVERITY() ,ERROR_STATE() ,ERROR_PROCEDURE() ,ERROR_LINE() ,ERROR_MESSAGE()

 

Maybe this will give you more insight as to what the issue is.


Wednesday, January 16, 2013 - 10:21:25 AM - Kevin Read The Tip

 

Thanks for responding Greg...Yes, I did use a real table name.

The actual query is...

EXEC sp_FindStringInTable '%APRN%', 'dbo', 'specialty_privileges'

 

Here is the code I copied and pasted:

 

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_FindStringInTable]    Script Date: 01/16/2013 09:24:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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'
   IF CURSOR_STATUS('variable', 'col_cursor') <> -3
   BEGIN
       CLOSE col_cursor  
       DEALLOCATE col_cursor
   END
END CATCH

 

 

 


Tuesday, January 15, 2013 - 5:46:25 PM - Greg Robidoux Read The Tip

@Kevin - did you try to put a real table name in instead of 'table_name'?  If the SP doesn't find the object it will give that generic error message.

 


Tuesday, January 15, 2013 - 4:29:43 PM - Kevin Read The Tip

 

I created the stored procedure without error but when I attempt to use it, I get the following error...

"There was an error"

This is all of the information that I am given.

I am using SQL 2008 server

EXEC sp_FindStringInTable '%APRN%', 'dbo', 'table_name'


Thursday, September 13, 2012 - 8:39:51 AM - Greg Robdoux Read The Tip

@Antony - try something like this:

 

declare @string varchar(20) = '%' + CHAR(13) + '%'
exec sp_FindStringInTable  @stringToFind = @string, @schema = 'dbo', @table = 'Table_2'


Wednesday, September 12, 2012 - 8:13:50 AM - Antony Read The Tip

Hi Greg,

 

Thank you for the quick responce.

 

I tried the sugestion, however i get the following error message:

"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'."

The script I used is below; (Obvoiusly ommiting real DB and Table names to protect the innocent and all. . . )

use []
go
exec sp_FindStringInTable  @stringToFind = '%' + CHAR(13) + '%', @schema = 'dbo', @table = ''

 

The SP name is underlined with a red zigzag line and the context message says;

'Could not find stored procedure 'sp_FindStringInTable'.'

In the @StringToFind value, the 1st '+' is underlined with a red zigzag line and the context message says;

'Incorrect syntax near '+'.'

And finally . . .

In the @StringToFind value, the 13 from 'CHAR(13)' is underlined with a red zigzag line and the context message says;

'Incorrect syntax near '13'. Expecting '(' or SELECT.'

 

Please help.

 

Thank you in advance.


Friday, September 07, 2012 - 8:59:15 AM - Greg Robidoux Read The Tip

@Antony - try something like this:

sp_FindStringInTable  @stringToFind = '%' + CHAR(13) + '%', @schema = 'dbo', @table = 'TestTable'


Friday, September 07, 2012 - 6:53:38 AM - Antony Read The Tip

Hi Greg,

 

Thank you for the very handy example and explanations. It works like a dream!

I now have a clean up job that requires me to find any 'CR' CHAR(13) values that have snuck into the data.

 

How would I modify the SP to look for CHAR(13) in the field values??

 

Thank you in advance

 

 

 

 

 


Tuesday, July 24, 2012 - 6:10:01 PM - Luis A Vaquerano Read The Tip

hi Greg Robidoux

it worked like a champ, thanks a millions

first I tested on a test table and it worked.

you da man!

thanks

 

 


Tuesday, July 24, 2012 - 4:39:14 PM - Greg Robidoux Read The Tip

@Luis - you could just use a simple REPLACE command to make the change you are requesting.

UPDATE SYS_Admissions SET AccountNumber = REPLACE(AccountNumber,'-','')

 

I would do this on a test table first just to check your work.


Tuesday, July 24, 2012 - 4:06:29 PM - Luis A Vaquerano Read The Tip

Hi;

I need some help; I have a scrip that will find an specific character in a field on a table

See below:

 

SELECT *, HospitalID AS Expr1, MCD_Status AS Expr2

FROM SYS_Admissions

WHERE (HospitalID = 15) AND (MCD_Status = N'pac')and (AccountNumber like '%-%')

 

That works fine; now I need to come up with a script that will delete the (dash) from the

Account number's field on records that have one.

In other words in Account Number field, I have account number that looks like this: 12345654-2345

I need to delete the dash from it so that it reads like this: 123456542345

 

please let me know and thanks in advance.

 

sincerely

 

Luis V

 


Thursday, July 19, 2012 - 3:09:17 PM - Greg Robidoux Read The Tip

Brian - looks like that approach would work as well.  Thanks for the alternative.

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tips' 
AND COLLATION_NAME is not null  


Thursday, July 19, 2012 - 1:04:34 PM - Brian Read The Tip

How about just "where COLLATION_NAME is not null" instead of the DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')' clause?  Looks like this hits all the text fields, even is another type is added in the next release.


Tuesday, August 31, 2010 - 9:02:11 AM - Mike Read The Tip
Nice example, if you remove the schema requirement and place the procedure in a specific table in SQL Server 2005, it will work just fine and it does not reside in the Master database.

 

Mike


Wednesday, April 15, 2009 - 3:20:24 PM - lualmeidasouza Read The Tip

Hello Alex!

Did you find what you needed?
I'm asking because in the procedure posted I also needed look for the numeric values and then I understood that for this, just add the "int, numeric etc ..." in line of the second cursor:

 

AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'', ''int'', ''numeric'')'

 

I hope I have helped and until the next time.

Have a good week

Luciana.
Excuse my English, I'm still learning...


Wednesday, April 15, 2009 - 3:15:15 PM - lualmeidasouza Read The Tip

Hello!

My name is Luciana and I'm a programmer (VB, VBA, ASP, SQL, Vbscript, JavaScript, Java and it's all... I think... ) and I saw an article at the site http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm and I liked!

Why?

Because a few days ago, I needed of a procedure that find a string in all the tables of my database, but I found just a procedure that find a string inside one, just one table of my database... and more... using cursor... yes! cursor... ok, ok, This was the site: http://www.mssqltips.com/tip.asp?tip=1522

I know that cursors are good when we are talking about diferents SGDBs, but it is assunt for another post...

Then, I changed the procedure for look inside all the tables in my database and I got... I was very happy!

And now, I found another code, another way to do this code! this is really fabulous.

Ok, thanks this tip! Thanks by the site! This is a good idea - share knowledge! Congradulations!!!!

And now, I want share with you my way of obtain this same result, but using cursor, ok?

Here is:

Code with change

CREATE PROCEDURE sp_FindStringInTable @stringToFind VARCHAR(100)

AS

--EXEC sp_FindStringInTable 'a'

DECLARE @sqlCommand VARCHAR(8000)

DECLARE @where VARCHAR(8000)

DECLARE @columnName sysname

DECLARE @tableName sysname

DECLARE @cursor VARCHAR(8000)

DECLARE @cursorTab varchar(8000)

set @cursorTab = 'DECLARE tab_cursor CURSOR FOR SELECT NAME

FROM SYSOBJECTS WHERE XTYPE = ''U''AND NAME <> ''dtproperties'''

--print @cursorTab

exec(@cursorTab)

open tab_cursor

fetch next from tab_cursor into @tableName

while @@fetch_status = 0

begin

SET @sqlCommand = 'SELECT * FROM ' + @tableName + ' WHERE'

 

BEGIN

--SET @sqlCommand = 'SELECT * FROM ' + @table + ' WHERE'

SET @where = ''

 

SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME

FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = ''' + @tableName + '''

AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'', ''int'', ''numeric'')'

 

EXEC (@cursor)

--print @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

FETCH NEXT FROM tab_cursor INTO @tableName

end

begin

close tab_cursor

DEALLOCATE tab_cursor

end

 

BEGIN

--PRINT 'There was an error'

IF CURSOR_STATUS('variable', 'col_cursor') <> -3

BEGIN

CLOSE col_cursor

DEALLOCATE col_cursor

END

END

 

 

 

 


Friday, July 11, 2008 - 8:28:57 AM - Heravar Read The Tip

Greetings,

 Im in the need of one of this searches, and I found a SP quite similar to this one, which uses a temp table instead of a cursor, but the idea is more or less the same...

 Though, I have an issue (I know that I dont actually NEED to do it this way, but im asking if its posible, because I've looking many alternatives and couldnt work it out):

 I'd like to do something like:
SELECT * FROM MyTable WHERE MyID IN (EXEC SearchAllFields paramSearchString )

(Actually, I want to do some joins, thats why I dont want to use the SP instead of the Select)

 

Of course, I cant use a SP as a Table, so i thought about using a view. A view is not possible, because it cant receive parameters. So the alternative would be using a function that returns a table.

That'd work wonderful, except for the fact that you cant:
Use EXEC in a function
Use #Tables in a function
Insert into normal tables in a function

So I guess that I will just have to use a SP... 

 Any ideas are welcome, thanks in advance!

 


Wednesday, June 18, 2008 - 2:33:41 PM - Alex33 Read The Tip

Works beautifully!  Thank you very much!  Now I'm looking for a "numeric" search sproc...




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.