Searching for a string in an all tables, rows and columns of a database

Submitted by Jochus on Tue, 14/04/2015 - 20:37 | Posted in: Database

Script has been developed and tested for SQL Server (version 2012)

    @search_string  VARCHAR(100),
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)
SET @search_string = 'FOOBAR'
DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE TYPE = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @TABLE_NAME, @table_id
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)
    OPEN columns_cur
    FETCH NEXT FROM columns_cur INTO @column_name
        SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @TABLE_NAME + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @TABLE_NAME + ', ' + @column_name + ''''
        FETCH NEXT FROM columns_cur INTO @column_name
    CLOSE columns_cur
    DEALLOCATE columns_cur
    FETCH NEXT FROM tables_cur INTO @TABLE_NAME, @table_id
CLOSE tables_cur
DEALLOCATE tables_cur


Submitted by CyCo on Thu, 16/04/2015 - 17:12

I suppose your script is only for Microsoft SQL Server (doesn't clearly say)?

Other RDBMS's can have similar or way easier solutions.

Submitted by Jochus on Mon, 20/04/2015 - 15:52

In reply to by CyCo

Yes, sorry, only Microsoft SQL server. I've added it to the blogpost.
I know Oracle, MySQL, ... have easier solutions. Any idea for SQL server? Couldn't find a better one :-( ...

Submitted by CyCo on Wed, 22/04/2015 - 10:20

In reply to by Jochus

Not that easy in SQL Server idd :(
Best solution would probably be to use a third party tool, like ApexSQL Search (free).

Your script should work, but it will be very slow when running it for a large database.

I did a quick Google search and found you probably got your script from here:
stackoverflow dot com slash questions slash 591853 slash search-for-a-string-in-all-tables-rows-and-columns-of-a-db

The comments in the answer also say it will be "RIDICULOUSLY slow" :)

The answer below that also suggests ApexSQL, or use a script with temporary tables instead of cursors, which will increase the speed significantly.

Add new comment

The content of this field is kept private and will not be shown publicly.


  • Lines and paragraphs break automatically.
  • You can caption images (data-caption="Text"), but also videos, blockquotes, and so on.
  • Web page addresses and email addresses turn into links automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <bash>, <cpp>, <css>, <html5>, <java>, <php>, <sql>, <xml>. The supported tag styles are: <foo>, [foo].