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?
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:
Build a dynamic SQL using sys.tables and sys.columns to perform the search (example here).
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).
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.
- create a database dump.
- 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