Here's what I think is a little upgrade to #6. This sp reduces human interaction to 1 step, but completes the entire search before showing you the results... so pick your poison

ALTER PROCEDURE [dbo].[sp_SearchTables] (

@TextPart varchar(1000)

)

AS

set nocount on

DECLARE @data table (

ind int identity(1,1),

query varchar(1000)

)

DECLARE @count int

DECLARE @iRow int

DECLARE @sql varchar(1000)

CREATE table #output (

Context varchar(255) null,

ColumnName varchar(255) null,

TableName varchar(255) null

)

INSERT INTO @data

select 'IF EXISTS(select [' + c.name + '] from [' + o.name

+ '] where [' + c.name + '] like ''%' + @TextPart + '%'')' + CHAR(13) +

'insert into #output '

+' select ' + c.name +',''' + c.name + ''',''' + o.name +''' from ' + o.name + ' where '

+ c.name + ' like ''%' + @TextPart + '%''' + CHAR(13) + CHAR(13)

from syscolumns c

inner join systypes t on t.xtype=c.xtype

inner join sysobjects o on o.id = c.id

where o.type = 'u' and c.id > 500 and t.name in ('varchar', 'char')

order by 1

SET @count = @@ROWCOUNT

SET @iRow = 1

WHILE @iRow <= @count

BEGIN

select @sql = query from @data where ind = @iRow

exec(@sql)

SET @iRow = @iRow + 1

END

select * from #output

drop table #output