Here is another nice one: "find string in datbase":

CREATE procedure [dbo].[sp_Find_String_In_DB]

@Search varchar(256)

as

declare @oid int

declare @colname varchar(256)

declare @tablename varchar(256)

declare @SelectProc varchar(256)

declare @Count int

declare table_cur cursor for

select object_id,name from sys.objects where type = 'U' /*and object_id = 326344277*/ order by name

open table_cur

fetch next from table_cur into @OID,@tablename

while @@Fetch_Status = 0 begin

declare column_cur cursor for

select name from sys.columns where object_id = @OID order by name --name = 'IsSharedACLOnly'

open column_cur

fetch next from column_cur into @colname

drop function dbo.spTempST

while @@Fetch_Status = 0 begin

begin try

SET @SelectProc = 'create function dbo.spTempST() returns int as begin return (select count(*) from [' + @tablename + '] where [' + @colname + '] like ''%' + @Search + '%'') end'

--set @SelectProc = dbo.LookupTable ( @tablename , @colname , @Search )

--PRINT @SelectProc

EXEC (@SelectProc)

exec @Count = spTempST

IF @Count > 0 begin

print 'Table ' + @tablename + ' Column ' + @colname + ' Matches ' + @Search

select @tablename,@colname

set @SelectProc = 'select * from ' + @tablename

EXEC (@SelectProc)

end

end try

begin catch

PRINT ERROR_MESSAGE()

end catch

begin try

drop function dbo.spTempST end try

begin catch end catch

fetch next from column_cur into @colname

end

close column_cur

deallocate column_cur

fetch next from table_cur into @OID,@tablename

end

close table_cur

deallocate table_cur

There is another Simple way:

SELECT * FROM SYSOBJECTS WHERE ID IN (SELECT ID FROM SYSCOMMENTS WHERE TEXT LIKE ‘%PRODUC%’)

The query produced all the object names. where ever it finds expression like ‘PRODUC‘

If you wanted to find any table name only then

SELECT * FROM SYSOBJECTS WHERE NAME LIKE ‘%TA%’ and xtype=‘u’

For stored procedure pass xtype=‘p’ and for views xtype=‘v’

If you wish to see the complete article :

http://blog.joggee.com/?p=119

Joggee

I have my own version of this and I wired it to a hotkey so I simply highlight any word in SSMS and hit my hotkey and it shows me everywhere it is used.

sqladvice.com/.../SQL-Server-Mana

CREATE procedure [dbo].[usp_stored_proc_find_text]

(

@searchtext1 nvarchar(100),

@searchtext2 nvarchar(100) = '',

@searchtext3 nvarchar(100) = '',

@searchtext4 nvarchar(100) = ''

)

AS

SELECT DISTINCT

name AS 'Name',

CASE

WHEN o.xtype = 'P' THEN 'Stored Procedure'

WHEN o.xtype = 'TR' THEN 'Trigger'

WHEN o.xtype = 'U' THEN 'Table'

WHEN o.xtype = 'D' THEN 'Constraint'

WHEN o.xtype = 'F' THEN 'Foreign Key'

WHEN o.xtype = 'FN' THEN 'Function'

WHEN o.xtype = 'PK' THEN 'Primary Key'

WHEN o.xtype = 'S' THEN 'System Table'

WHEN o.xtype = 'UQ' THEN 'Index'

WHEN o.xtype = 'V' THEN 'View'

ELSE 'Other'

END AS 'Type Description',

o.xtype AS 'Type'

FROM sysobjects o, syscomments s

WHERE

o.id = s.id

AND

text LIKE '%'+@searchtext1+'%'

AND

text LIKE '%'+@searchtext2+'%'

AND

text LIKE '%'+@searchtext3+'%'

AND

text LIKE '%'+@searchtext4+'%'

--AND

-- (o.xtype = 'P' OR o.xtype = 'TR' OR o.xtype = 'V')

ORDER BY

o.xtype, name

hello

find string in datbase error in

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'spTempST'. The stored procedure will still be created.