You can try this. You will obviosuly need to uncomment the exec at the end for it to do anything.

CREATE proc search_tables @table sysname = 'ALL',

@string varchar(8000)

as

declare @sql varchar(8000),

@separator varchar(10),

@current_table sysname

IF @table = 'ALL'

BEGIN

DECLARE loop_tables CURSOR

FOR SELECT name FROM sysobjects WHERE type = 'U'

END

ELSE

BEGIN

IF OBJECTPROPERTY(OBJECT_ID(@table),'IsUserTable') = 1

BEGIN

DECLARE loop_tables CURSOR

FOR SELECT name FROM sysobjects

WHERE type = 'U'

AND name = @table

END

ELSE

BEGIN

PRINT 'Invalid or System table'

RETURN

END

END

OPEN loop_tables

FETCH loop_tables into @current_table

WHILE (@@fetch_status) = 0

BEGIN

select @sql = 'select * from '+@table,

@separator = char(13)+char(10)+'WHERE '

select @sql = @sql + @separator + sc.name + ' LIKE '''+@string+'''',

@separator = char(13)+char(10)+'OR '

from syscolumns sc

where id = object_id(@current_table)

and type in (35,39,47)

select @sql

--exec (@sql)

FETCH loop_tables into @current_table

END

CLOSE loop_tables

DEALLOCATE loop_tables