/*
SQL Grep (SQL server 2000 version)

Full details: http://soerennielsen.wordpress.com/2007/08/22/find-the-needle-in-the-sql-database/

Thanks to "Sootie" for converting script to 2000 format. 
*/


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 sysobjects ao
	on so.id = ao.id
	left join sysusers ss
	on ao.uid = ss.uid
	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