This one was found by Dave Kay, a co-worker. It has come in handy already and I’m sure I will use it again.
Warning: it can be a pig – it is the kind of query you want to run on a mirror database… overnight… on a weekend… preferably a long weekend… from someone else’s workstation… while wearing gloves and a ski mask.
DECLARE @SearchStr NVARCHAR(100) SET @SearchStr = 'search' -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 -- Date modified: 28th July 2002 22:50 GMT DECLARE @Results TABLE ( columnname NVARCHAR(370), columnvalue NVARCHAR(3630) ) SET nocount ON DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128), @SearchStr2 NVARCHAR(110) SET @TableName = '' SET @SearchStr2 = Quotename('%' + @SearchStr + '%', '''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = (SELECT Min(Quotename(table_schema) + '.' + Quotename(table_name)) FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND Quotename(table_schema) + '.' + Quotename(table_name) > @TableName AND Objectproperty(Object_id(Quotename(table_schema) + '.' + Quotename(table_name)), 'IsMSShipped') = 0) WHILE ( @TableName IS NOT NULL ) AND ( @ColumnName IS NOT NULL ) BEGIN SET @ColumnName = (SELECT Min(Quotename(column_name)) FROM information_schema.columns WHERE table_schema = Parsename(@TableName, 2) AND table_name = Parsename(@TableName, 1) AND data_type IN ( 'char', 'varchar', 'nchar', 'nvarchar' ) AND Quotename(column_name) > @ColumnName) IF @ColumnName IS NOT NULL BEGIN INSERT INTO @Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) ' + 'FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT columnname, columnvalue FROM @Results
4 thoughts on “Full database search for text”
Wow, what would you ever need to use a full database search for? Pretty interesting though.
Databases tend to be incredibly slow at doing any string manipulation. Wouldn’t it be easier to pull all the data into C#, PHP, whatever and then parse the text from there? Heck, you don’t even need the database at all, you could back the entire database up as an SQL file, and then scan it using your code from there.
In this case we were searching for the occurrence of a particular e-mail address. We looked in all expected locations with no luck. So we tried using this to see if it was somewhere in the database that we hadn’t thought of.
Brute force may be ugly, but sometimes it is the most effective – the Mongols didn’t have shiny armour or pretty castles, but they still carved out the largest empire the world had ever seen.
Did you end up finding the email address you were looking for?
Databases are fast when handling data relationships, but if you’re doing a string comparison search through ALL of the data, I assume that it’s incredibly inefficient.
Like I mentioned above, using C# to scan through an SQL data dump of the database would probably be faster.
No, I didn’t find the email address. I suspect that the address in question was actually in one of the flat files and not in the db after all.
You’re right: an scan of a data dump would probably be faster, but may not be as convenient. Sometimes Quick and Dirty is easier, even if it is Slow and Dirty.