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)

DECLARE
    @search_string  VARCHAR(100),
    @TABLE_NAME     SYSNAME,
    @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
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
    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
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @TABLE_NAME + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @TABLE_NAME + ', ' + @column_name + ''''
 
        EXECUTE(@sql_string)
 
        FETCH NEXT FROM columns_cur INTO @column_name
    END
 
    CLOSE columns_cur
    DEALLOCATE columns_cur
 
    FETCH NEXT FROM tables_cur INTO @TABLE_NAME, @table_id
END
 
CLOSE tables_cur
DEALLOCATE tables_cur

Comments

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.

Full HTML

  • 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>, <javascript>, <php>, <sql>, <xml>. The supported tag styles are: <foo>, [foo].
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.