How to find a string inside a entire database?

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?

Answers


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.


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.


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


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

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

SQL Locator (free) has worked great for me. It comes with a lot of options and it's fairly easy to use.


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.


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/


Sorry for the late answer, but I just had this question as well and ended up resolving it using another approach that is probably more generic to all databases.

  1. create a database dump.
  2. From there you should be able to open the file in a text editor and search for the required string.

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

Need Your Help

WooCommerce - Move Product Description out Tabs into Summary

woocommerce

I've hunted high and low how to move the Product Description on a Single Product page of Woocommerce out of the tabs and in to the main section. I just can't find it anywhere!

Remove all attributes

javascript jquery

Is it possible to remove all attributes at once using jQuery?