How to find a string inside a entire database?

Question

I have one specific string, such as "123abcd" for example but I don't know the name of the table or even the name of the column inside the table on my SQL Server Database. I want to find it with a select and show all columns of the related string, so I was wondering something like:

select * from Database.dbo.* where * like  '%123abcd%'

For obvious reasons it doens't work, but there is a simple way to create a select statement to do something like this?

Majolica

Why don't you ask the system developers or the DBA where to find it? It's things like this that have the developers and DBAs at odds ;) – Phalanx

How big is the database? Is performance an issue? – Wivinia

3GB database, no problem with performance. – Majolica

You're going to have to write some really yucky dynamic SQL and query the system catalog (sys.schemas, sys.tables, sys.columns) to do this. – Femme

This functionality was available in phpmyadmin with MySQL! Ah the pains.. – Serviceman

Answer 1

This will work:

DECLARE @MyValue NVarChar(4000) = 'something';

SELECT S.name SchemaName, T.name TableName
INTO #T
FROM sys.schemas S INNER JOIN
     sys.tables T ON S.schema_id = T.schema_id;

WHILE (EXISTS (SELECT * FROM #T)) BEGIN
  DECLARE @SQL NVarChar(4000) = 'SELECT * FROM $$TableName WHERE (0 = 1) ';
  DECLARE @TableName NVarChar(1000) = (
    SELECT TOP 1 SchemaName + '.' + TableName FROM #T
  );
  SELECT @SQL = REPLACE(@SQL, '$$TableName', @TableName);

  DECLARE @Cols NVarChar(4000) = '';

  SELECT
    @Cols = COALESCE(@Cols + 'OR CONVERT(NVarChar(4000), ', '') + C.name + ') = CONVERT(NVarChar(4000), ''$$MyValue'') '
  FROM sys.columns C
  WHERE C.object_id = OBJECT_ID(@TableName);

  SELECT @Cols = REPLACE(@Cols, '$$MyValue', @MyValue);
  SELECT @SQL = @SQL + @Cols;

  EXECUTE(@SQL);

  DELETE FROM #T
  WHERE SchemaName + '.' + TableName = @TableName;
END;

DROP TABLE #T;

A couple caveats, though. First, this is outrageously slow and non-optimized. All values are being converted to nvarchar simply so that they can be compared without error. You may run into problems with values like datetime not converting as expected and therefore not being matched when they should be (false negatives).

The WHERE (0 = 1) is there to make building the OR clause easier. If there are not matches you won't get any rows back.

Femme

Answer 2

Here are couple more free tools that can be used for this. Both work as SSMS addins.

ApexSQL Search – 100% free - searches both schema and data in tables. Has couple more useful options such as dependency tracking…

SSMS Tools pack – free for all versions except SQL 2012 – doesn’t look as advanced as previous one but has a lot of other cool features.

Calamine

Can confirm, ApexSQL saved me and it is up to date, connected with latest SQL Server and Visual Studio. – Professor

ApexSQL starts at SMSS 2012, so for 2008 R2 I had to use SMSS Tools Pack, but this worked for me. – Boyett

If you prefer/need a command-line tool, I wrote an open-source one here: github.com/nzbart/sqlgrep – Halfcaste

Answer 3

I think you have to options:

1. Build a dynamic SQL using sys.tables and sys.columns to perform the search (example here).

2. Use any program that have this function. An example of this is SQL Workbench (free).

Otis

Answer 4

create procedure usp_find_string(@string as varchar(1000))
as
begin
declare @mincounter as int
declare @maxcounter as int
declare @stmtquery as varchar(1000)
set @stmtquery=''
create table #tmp(tablename varchar(128),columnname varchar(128),rowid int identity)
create table #tablelist(tablename varchar(128),columnname varchar(128))
declare @tmp table(name varchar(128))
declare @tablename as varchar(128)
declare @columnname as varchar(128)

insert into #tmp(tablename,columnname)
select a.name,b.name as columnname from sysobjects a
inner join syscolumns b on a.name=object_name(b.id)
where a.type='u'
and b.xtype in(select xtype from systypes
    where name='text' or name='ntext' or name='varchar' or name='nvarchar' or name='char' or name='nchar')
order by a.name

select @maxcounter=max(rowid),@mincounter=min(rowid) from #tmp 
while(@mincounter <= @maxcounter )
begin
 select @tablename=tablename, @columnname=columnname from #tmp where rowid=@mincounter
 set @stmtquery ='select top 1  ' + '[' +@columnname+']' + ' from ' + '['+@tablename+']' + ' where ' + '['+@columnname+']' + ' like ' + '''%' + @string + '%'''
 insert into @tmp(name) exec(@stmtquery)
 if @@rowcount >0
 insert into #tablelist values(@tablename,@columnname)
 set @mincounter=@mincounter +1
end
select * from #tablelist
end

Scourge

Answer 5

In oracle you can use the following sql command to generate the sql commands you need:

select 
     "select * "
     " from "||table_name||
     " where "||column_name||" like '%123abcd%' ;" as sql_command
from user_tab_columns
where data_type='VARCHAR2';

Furan

Thank you, nice method. Just it looks like second line should end with || as well. – Bolton

Answer 6

Common Resource Grep (crgrep) will search for string matches in tables/columns by name or content and supports a number of DBs, including SQLServer, Oracle and others. Full wild-carding and other useful options.

It's opensource (I'm the author).

http://sourceforge.net/projects/crgrep/

Dollop

Answer 7

I usually use information_Schema.columns and information_schema.tables, although like @yuck said, sys.tables and sys.columns are shorter to type.

In a loop, concatenate these

@sql = @sql + 'select' + column_name + 
' from ' + table_name + 
' where ' + column_name ' like ''%''+value+''%' UNION

Then execute the resulting sql.

Acceptance

I believe the INFORMATION_SCHEMA stuff is deprecated. Although they do provide some values that the sys DMVs don't. One of these is the ordinal position of a column in a table. I've had to rely on the old views for that a few times. – Femme

Answer 8

Here is an easy and convenient cursor based solution

DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)

SET @search_string = 'StringtoSearch'

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

Supination

Answer 9

Here is the PostgreSQL version of @Raihan's answer at https://mcmap.net/q/377174/-how-to-find-a-string-inside-a-entire-database . It also makes use of the answer at https://mcmap.net/q/111101/-how-to-get-a-list-column-names-and-datatypes-of-a-table-in-postgresql .

SELECT 
     'select * from ' || table_name || ' where ' || column_name || ' like ''%123abcd%'';' as sql_command
FROM
(
    SELECT
        pg_class.relname AS table_name,
        pg_attribute.attname AS column_name,
        pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type
    FROM
        pg_catalog.pg_attribute
    INNER JOIN
        pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
    INNER JOIN
        pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    WHERE
        pg_attribute.attnum > 0
        AND NOT pg_attribute.attisdropped
        -- uncomment below filter if you want to check only the tables in a specific schema
        -- AND pg_namespace.nspname = '<your-schema-name-here>'
        AND pg_class.relname NOT LIKE 'IX%'
    ORDER BY
        1,2,3 ASC) tmp
where data_type NOT IN ('uuid', 'integer', 'boolean', 'bigint', 'real', 'timestamp without time zone');

Bolton