Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (52)   |   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

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

Example Uses

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:






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





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, September 02, 2016 - 5:23:08 PM - Scott Back To Top

After reading all the comments I thought I'd add a SQL 2005 (and later) compatible version that accepts a search string and a list of tables (as an XML parameter).

-- Search all character columns a list of tables for a target string
CREATE PROCEDURE dbo.FindStringInTable
    @search VARCHAR(100),
    @tables_arg XML
AS
-- Extract the list of table names from @tables_arg into a table variable
DECLARE @tables TABLE (name sysname);
INSERT INTO @tables ( name )
SELECT  c.value('.', 'sysname')
FROM    @tables_arg.nodes('/tables/name') n(c);

DECLARE @sqlCommand VARCHAR(MAX);    SET @sqlCommand = '';

SELECT  @sqlCommand = @sqlCommand + 'SELECT TableName = ''' + TableName + ''', * FROM ' + TableName + ' WHERE ' + Filters + ';' + CHAR(10)
FROM (
    -- Get the schema-qualified name of each table whose name appears in @tables_arg
    SELECT  object_id, TableName = QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) 
    FROM sys.tables
    WHERE name IN (SELECT name FROM @tables)
) tbls
CROSS APPLY (
    -- Generate the complete WHERE clause by concatenating all the char columns into " OR [column] LIKE 'search' " expressions
    -- Remove the leading ' OR ' with STUFF()
    SELECT Filters = STUFF( ( SELECT ' OR [' + name + '] LIKE ''' + @search + ''''
                              FROM sys.columns 
                              WHERE object_id = tbls.object_id AND collation_name IS NOT NULL
                              FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 4, '')
) cols;

PRINT @sqlCommand;
EXEC (@sqlCommand);
GO

DECLARE @tables_arg XML; SET @tables_arg = '<tables><name>SearchTable 1</name><name>SearchTable 2</name></tables>';
EXEC dbo.FindStringInTable '%xyzzy%', @tables_arg;

If there is some process for deciding which tables to search, this query will create valid XML for @tables_arg

SELECT name
FROM (
    SELECT name = 'SearchTable 1'
    UNION ALL
    SELECT name = 'SearchTable 2'
) t
FOR XML PATH(''), ROOT('tables');

If you wanted the table list to have schema-qualified names ( like " <name>dbo.Table1</name><name>abc.Table2</name> " ), the sys.tables subquery can be modified to:

    FROM sys.tables tbl
    INNER JOIN (
        SELECT  schema_id = SCHEMA_ID(ISNULL(PARSENAME(name, 2), 'dbo')), name = PARSENAME(name, 1)
        FROM  @tables
    ) arg ON tbl.schema_id = arg.schema_id AND tbl.name = arg.name

 


Friday, July 22, 2016 - 9:50:33 AM - Misbah Dabhoiwala Back To Top

Hi,

resolved it, simple declaration change due to SQL 2005. Works perfect now!!

 

Thanks

 

 


Friday, July 22, 2016 - 6:37:46 AM - Misbah Dabhoiwala Back To Top

 

HI, I'd like to know what minimum version of sql server is required to execute this.

 

i get the following errors and im assuming its a version issue

Msg 139, Level 15, State 1, Procedure sp_FindStringInTable, Line 5
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure sp_FindStringInTable, Line 8
Must declare the scalar variable "@sqlCommand".
Msg 137, Level 15, State 2, Procedure sp_FindStringInTable, Line 14
Must declare the scalar variable "@sqlCommand".
Msg 137, Level 15, State 2, Procedure sp_FindStringInTable, Line 15
Must declare the scalar variable "@sqlCommand".
Msg 137, Level 15, State 2, Procedure sp_FindStringInTable, Line 16
Must declare the scalar variable "@sqlCommand".

Thanks

 


Wednesday, May 04, 2016 - 8:09:20 AM - Greg Robidoux Back To Top

Hi Espen,

any code that dynamically builds a SQL statement is prone to SQL injection.  This was originally meant to be use as a DBA tool to help find data not as a general stored procedure for application wide use.

You could tighten this up by checking the incoming parameter values prior to building the statement.

-Greg

 


Wednesday, May 04, 2016 - 3:24:09 AM - espen Back To Top

Is this SP not prone to SQL injection?


Friday, April 08, 2016 - 2:43:49 PM - James Moore Back To Top

Greg,

UPDATE: I was able to get multiple table included using a variable as a table alias, but its dog slow. Probably the best solution is to avoid using cursors all together. Any suggestions?

PREVIOUS: Is it possible to have multiple tables included?  Not all tables, just the ones I specify. I tried to add:  OR TABLE_NAME = ''WebLocations''  (but not working as expected).

BEGIN

-- Gets all columns names for bcpao.WebProperties table

SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME 

FROM BCPAOWebData.INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_SCHEMA = ''bcpao'' 

 AND TABLE_NAME IN (''WebProperties'',''WebLocations'',''WebOwners'')

 AND COLUMN_NAME IN (''ParcelID'',''CondoName'',''OwnerName'',''LocAddressFormatted'')

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

 

--EXEC SP_SearchTables @Tablenames = '%WebProperties,WebOwners,WebLocations%' 

   --                            , @SearchStr = '%WINSTEAD%' 

   --                            , @GenerateSQLOnly = 0

  

EXEC (@cursor) 

 

OPEN col_cursor FETCH NEXT FROM col_cursor INTO @columnName    

 

WHILE @@FETCH_STATUS = 0    

BEGIN    

IF @v_where <> '' SET @v_where = @v_where + ' OR '

IF @columnName = 'ParcelID' SET @v_tableAlias = 'p.'

IF @columnName = 'CondoName' SET @v_tableAlias = 'p.'

IF @columnName = 'OwnerName' SET @v_tableAlias = 'o.'

IF @columnName = 'LocAddressFormatted' SET @v_tableAlias = 'l.'

 

SET @v_where = @v_where + 'CONTAINS(' + @v_tableAlias + @columnName + ', ''' + @p_basicSearchKeyword + ''') OR CONTAINS(' + @v_tableAlias + @columnName + ', ''FORMSOF(THESAURUS, "' + bcpao.FormatSearchInput(@p_basicSearchKeyword) + '")'')'

 

--SET @v_where = @v_where + 'CONTAINS(p.' + @columnName + ',''' + @p_basicSearchKeyword + ''') OR CONTAINS(p.' + @columnName + ', ''FORMSOF(THESAURUS, "' + bcpao.FormatSearchInput(@p_basicSearchKeyword) + '")'')'

 

FETCH NEXT FROM col_cursor INTO @columnName      

END   

 

CLOSE col_cursor    

DEALLOCATE col_cursor

END


Friday, March 25, 2016 - 2:06:20 AM - krishantha Fernando Back To Top

 

 Thanks, This is very helpful and accurate


Wednesday, March 16, 2016 - 2:07:44 PM - Greg Robidoux Back To Top

Hi James,

It is not a simple change the way the code is written to look at multiple tables. 

You would have to create a loop to build multiple statements and then execute each of these.

I will see if I can write some new code to search a list of tables.

-Greg


Wednesday, March 16, 2016 - 10:30:17 AM - James Back To Top

Hi Greg,

Is it possible to have multiple tables included?  Not all tables, just the ones I specify.

I tried to add:  OR TABLE_NAME = ''WebLocations''  (but not working as expected).

Thanks for any help in advance.

 

BEGIN

-- Gets all columns names for bcpao.WebProperties and bcpao.WebLocations tables

SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME 

FROM BCPAOWebData.INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_SCHEMA = ''bcpao'' AND TABLE_NAME = ''WebProperties'' OR TABLE_NAME = ''WebLocations'' 

AND COLUMN_NAME IN (''Owner1'',''Owner2'',''Owner3'',''ParcelID'',''LocAddressFormatted'',''CondoName'')

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

  

EXEC (@cursor)

 

OPEN col_cursor FETCH NEXT FROM col_cursor INTO @columnName

WHILE @@FETCH_STATUS = 0

BEGIN

IF @v_where <> '' SET @v_where = @v_where + ' OR '

SET @v_where = @v_where + 'CONTAINS(' + @columnName + ',''' + @p_basicSearchKeyword + ''') OR CONTAINS(' + @columnName + ', ''FORMSOF(THESAURUS, "' + bcpao.FormatSearchInput(@p_basicSearchKeyword) + '")'')'

FETCH NEXT FROM col_cursor INTO @columnName

END

CLOSE col_cursor

DEALLOCATE col_cursor

 

END

 


Friday, February 26, 2016 - 9:30:41 AM - Greg Robidoux Back To Top

Hi Russ,

did this SP get created in the master database?

when you run this from a query window are you in the correct user database?

Sorry if these seems like trivial questions, just not sure why this would be failing.


Thursday, February 25, 2016 - 4:56:47 PM - Russ Mittler Back To Top

 Hi Greg, what I mean by running the SP manually is that I can see the SP on the database and I right click it, and execute from the GUI. When I do that, it returns a value of 6. However, when I run it the way demostrated in this article, it gives me the error. 

I know for 100% the schema and table do exist as when I run the SP the way descibed above, it returns a value. I copy/paste the info from this and run as demonstrated and I get the error... 

Thanks! 

 


Thursday, February 25, 2016 - 3:08:35 PM - Greg Robidoux Back To Top

Hi Russ,

Can you double check to make sure you are using the correct schema "dbo" and table "MessageHeader". The error you are getting is generally from when the schema and table are not found.

Also, can you let me know what you mean by execute the SP manually?

Thanks
-Greg


Thursday, February 25, 2016 - 2:06:01 PM - Russ Mittler Back To Top

 Hello - Whenever I try and execute, I get the following error:

There was an error. Check to make sure object exists.

I ran the script to create the SP and when I execute the SP manually, it shows a result of 6. But when I try and run it like in your example:

exec sp_FindStringInTable '123456', 'dbo', 'MessageHeader'

I get the above error... what am I doing wrong?

 

 


Monday, December 14, 2015 - 1:39:44 PM - Greg Robidoux Back To Top

Hi db81,

The code above is expecting to be run inside a database, this was setup to provide the database name as you show in your example.

The way it is getting the column names is using the schema to look up from that view.  You would have to change this as well.

Could you post your modified code and I can see where there is an issue.

Thanks
Greg


Monday, December 14, 2015 - 12:51:03 PM - db81 Back To Top

Hy,

I am having a problem trying to get this working from my c# app (I modified your proc a little bit and moved @shema out from it).

 internal static DataTable FindStringInTable(string chars, string table)
        {
            DataTable dt = new DataTable();
           
            SqlConnection conn = new SqlConnection(Program.connstring);

            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand("obrPla_FindStringInTable", conn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.AddWithValue("@stringToFind", chars);               
                da.SelectCommand.Parameters.AddWithValue("@table", table);

                da.Fill(dt);

                return dt;
            }
            catch (SqlException ex) { throw ex; }
            catch (Exception ex) { throw ex; }
            finally
            {
                conn.Close();
            }
        }

 

So, when the table is being filled, this is sent to DB:

exec obrPla_GetStringFromTable @stringToFind=N'staj',@table=N'[ObrPla].[dbo].[Naselja]'

but only thing I get back is empty table, and executing this inside a DB query gives me notification "'There was an error. Check to make sure object exists.' "

 

But, if I try to execute

exec obrPla_GetStringFromTable '%staj%','Naselja'

from DB query, I get back the results I was looking for.

 

What am I doing wrong with params?

 

Thx for any help.

 

 

 

 

 

 


Friday, September 18, 2015 - 9:52:34 AM - Greg Robidoux Back To Top

Hi Carlos,

I tried this a few ways and it is working for me.

Make sure you are using the % before and after if you are looking for a string that is part of a bigger string.

-Greg


Sunday, September 13, 2015 - 6:08:07 AM - carlos moran Back To Top

hi

why the procedure only find words incmplete like 'Irv' or 'land' i mean why not search for Irvng o Rolando im try with my database and if i search por complete Word like 'Seguro' no works but if i search for' Segur' this Works fine

thanks


Tuesday, September 08, 2015 - 6:29:05 AM - Arshad Back To Top

This is awesome! Thanks Greg!


Tuesday, July 21, 2015 - 10:46:34 AM - James Back To Top

Greg,

You are the man!  This T-SQL is perfect for what I need.

Thank you very much!


Friday, June 26, 2015 - 1:14:06 PM - Greg Robidoux Back To Top

Hi Aaron, what you did works well.  I included this in the tip in case someone to do an exact search too.

 

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%'.

 

Thanks

Greg


Friday, June 26, 2015 - 12:16:27 PM - Aaron Seeling Back To Top

Thank you for this procedure! Helped greatly in finding what I needed. Before I was able to get it working, I had to update the line

SET @where = @where + ' [' + @columnName + '] LIKE ''%' + @stringToFind + '%''' 

To include %, since otherwise it wouldn't be a true wildcard search.

I do realize you may have left it out to give the procedure more flexibility though :).

Thanks again!


Friday, March 06, 2015 - 8:22:16 PM - Jen Back To Top

This is awesome! Thanks Greg! Is there an easy way to replace all these values I have just found?


Wednesday, January 28, 2015 - 5:30:17 AM - Dorghamy Back To Top

pls l want delete the value search after execute storedproc


Friday, January 09, 2015 - 3:40:37 PM - Greg Robidoux Back To Top

Hi Tom, is there a table named dbo.Test in the database you are checking?

-Greg


Thursday, January 08, 2015 - 2:56:16 AM - Tom Back To Top

I have written 

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

Test is my tablename

Yet,I get folloeing error

There was an error. Check to make sure object exists.



Sunday, December 07, 2014 - 2:03:25 AM - Umidillo Buzurhanov Back To Top

Hi!

I am not advanced user of T-SQL but I am feeling that sp_FindStringInTable

function can simply the following quiry use for obtaining production results.

As you can see, it not only contains like but also other operators as '=' 'and' not like

I would really appreciate if you could modify function of sp_FindStringInTable in a way that would result the same as the below quiry

===============================================

SELECT COND_NO = '550', * FROM PRODUCED_FULL

WHERE

MYEAR = '2014' AND

CPL = 'XA1' AND

MODEL = '1JX69' AND

SOURCE = 'EC' AND

(FULL_OPTION LIKE '%MX3%') AND

(FULL_OPTION LIKE '%YC2%') AND

(FULL_OPTION LIKE '%C60%') AND

(FULL_OPTION LIKE '%JM4%') AND

(FULL_OPTION LIKE '%ADL%') AND

(FULL_OPTION NOT LIKE '%J41%' AND

FULL_OPTION NOT LIKE '%AJ3%')

===============================================


Monday, August 18, 2014 - 10:04:03 PM - Cesar Back To Top

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 Back To Top

hehe - @Rajesh

 

Please check datatypes. you may get idea by yourself.

 

Thanks


Wednesday, July 17, 2013 - 9:11:32 AM - Rajesh Back To Top

Its only for string value what about date field then?


Thursday, April 25, 2013 - 11:15:54 AM - suprduprman Back To Top

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) Back To Top

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 Back To Top

 

@Greg - THANKS...That worked!


Wednesday, January 16, 2013 - 5:37:52 PM - Greg Robidoux Back To Top

@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 Back To Top

 

@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 Back To Top

@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 Back To Top

 

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 Back To Top

@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 Back To Top

 

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 Back To Top

@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 Back To Top

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 Back To Top

@Antony - try something like this:

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


Friday, September 07, 2012 - 6:53:38 AM - Antony Back To Top

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 Back To Top

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 Back To Top

@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 Back To Top

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 Back To Top

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 Back To Top

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 Back To Top
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 Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

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


Learn more about SQL Server tools