[AKA: SQL Grep]

[Updated Nov 11 2008]

[Updated March 10 2009]

A few years ago I was debugging an annoying SharePoint 2003 issue with some wrong usernames after renaming them. It turned out that I had to dig deep into the SQL database tables to figure out how everything worked (nobody cares about the actual result anymore).

To do that I had to write a piece of SQL scripting that could search the database like: “search for a string in any column in any record in any table in the current database”. The script was actually quite useful (and hard to write) so I’ve kept it for a few years.

The other day I had another strange problem with the new SharePoint server and had to search the database for a specific guid. I dusted off my old script and it turned out to be very useful again. I updated it slightly and figure that it might be useful for you too.

So here it is.

There are really two parameters that you need to provide. “@searchText” is the search string and “@datatype” is the datatype to search (i.e. what types of columns that should be searched).

“@datatypes” values (select system_type_id, name from sys.types order by name):

Datatype value

SQL DataType

null

All types of strings*

-1

All types of numbers*

127

Bigint

173

Binary

104

Bit

175

Char

61

Datetime

106

Decimal

62

Float

34

Image

56

Int

60

Money

239

Nchar

99

Ntext

108

Numeric

231

Nvarchar

59

Real

58

smalldatetime

52

Smallint

122

Smallmoney

98

sql_variant

231

Sysname

35

Text

189

Timestamp

48

Tinyint

36

uniqueidentifier

165

Varbinary

167

Varchar

241

Xml

* I added special support for the first two options

Remarks:

  • The script is not actually a function or a stored procedure because I usually use it by copying the SQL into a SQL query window and execute. I don’t want to create a new function/stored procedure in the databases that I’m peeking into.
  • You can only search datatypes that are convertible to a “nvarchar” column (and only the first 4096 characters are searched), i.e. pretty much everything barring “image” and “timestamp” (see table on http://msdn2.microsoft.com/en-us/library/ms187928.aspx)
  • Every value that is searched is converted to a “nvarchar” column by using default options, i.e. dates and thousand separators will follow your database settings
  • The reason I convert everything is that if you search for a number you probably want to search all number like columns, not just “short” or “long” etc., likewise you’ll want to search every type of text column, not just the one of “char”, “varchar”, “nvarchar”, “text” etc.
  • Is it slow? Absolutely. I wanted to find the string I was searching for, not a query to be part of any production systems
  • Add “%” to search for wildcards (i.e. before and after your string)
  • No it does not search every database only the current one. It’s easy/possible to add a search through all databases, but I didn’t really need it
  • Output is a list of found values with table name, column name and the actual value found (might be interesting if you used wildcards)

Finally! The actual script (copy/paste + modify params):

declare @searchText varchar(255)
set @searchText = '%mystring%'

declare @datatype int
set @datatype = null

/*Set type of coloumn to search

null All types of strings*
-1 All types of numbers*
127 Bigint
173 binary
104 Bit
175 Char
61 datetime
106 decimal
62 Float
34 image
56 Int
60 money
239 nchar
99 ntext
108 numeric
231 nvarchar
59 Real
58 smalldatetime
52 smallint
122 smallmoney
98 sql_variant
231 sysname
35 text
189 timestamp
48 tinyint
36 uniqueidentifier
165 varbinary
167 varchar
241 xml
*/

declare @innerSQL varchar(4000)

DECLARE tables CURSOR READ_ONLY
FOR (
	select so.name as TableName, sc.name as ColName, ss.name as sch
	from syscolumns sc left join sysobjects so
	on sc.id = so.id
	left join sys.all_objects ao
	on so.id = ao.object_id
	left join sys.schemas ss
	on ao.schema_id = ss.schema_id
	where OBJECTPROPERTY(so.id, N'IsUserTable') = 1
		and (sc.xtype = @datatype
		or (@datatype is null and collation is not null)
		or (@datatype = -1 and sc.xprec>0))) -- to include text only

create table ##found ( tablename varchar(255), colname varchar(255), val nvarchar(4000) )

DECLARE @tablename varchar(255)
declare @colname varchar(255)
declare @sch varchar(255)

OPEN tables

FETCH NEXT FROM tables INTO @tablename, @colname, @sch

WHILE (not @@fetch_status = -1)
BEGIN
	IF (not @@fetch_status = -2)
	BEGIN
		--print 'table: ' + @sch + '.' + @tablename + ', column: '+ @colname
		set @innerSql = 'insert into ##found (tablename, colname, val) select '''
		+ @sch + '.' + @tablename + ''', ''' + @colname + ''', Convert(nvarchar(4000),['
		+ @colname + ']) from ' + @sch + '.[' +@tablename + '] where Convert(nvarchar(4000),['
		+ @colname + ']) like ''' + @searchText + ''' '

		exec( @innerSql )

	END
	FETCH NEXT FROM tables INTO @tablename, @colname, @sch
END

CLOSE tables

DEALLOCATE tables

--Output result
select *
from ##found
drop table ##found

I hope some of you will find it useful, if not at least I have a place to find it the next time I need it ;-)

Enjoy.

Download

[Added March 10 2009]

For your convenience I’ve added download links

Sql Grep (2005 version)

Sql Grep (2000 version) – Thanks Sootie!