I have shared this code with others in the fourms but I think it is good enough for me to blog. I create a proc that will go through every db on the server or one you sepcify and look through all code object (proc, view, function, etc) for a particular string you give it (@search). This is one of the most powerful procs you can have. The reason is clear. For example, lets say you wrote a proc last month and you dont remember what you called it, if you remember anything that was in it, you can pass that string into the proc and it will find it. It is used by every database developer I show it to. Now it can be yours for free! create table FIND_WORKING (databasename varchar(15),name varchar(70), type varchar(30)) GO use mydatabase --***************************************************************** -- Proc Name: Find -- -- Purpose: This proc will find any string in all SQL code on this -- server. It will not look in the data itself, but only -- in code -- -- Called By: anyone -- -- Created By: Michael F. Berry -- -- Created on: 12/19/2006 -- -- Modified By: Michael F. Berry -- -- Modified Dte:1/25/2007 -- -- Modified Rsn:Make it put out into one main recordset for clarity --*****************************************************************CREATE Procedure [dbo].[Find] (@search varchar (150),@DB varchar (200) = 'ALL' )as set nocount on--declare @search varchar(150) --set @search = 'temp'declare @databasename as varchar(200)truncate table dba.dbo.find_workingselect 'Searching For: '''+ @search + ''''declare Curse cursor local fast_forwardfor select distinctnamefrom master .dbo.sysdatabases wherename not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW','indataDB_main','IndataDB_MarketValues','IndataDB_PriorMonth','PartnerControl','PartnerData') and(@db = 'all'or name = @db)open Curse fetch next from Curse into @databasenamewhile @@fetch_status = 0beginexec ('use ' + @databasename + ' insert into DBA.dbo.find_working select distinct ''' + @databasename + ''', cast(o.[name] as varchar(40)) as objectname, o.type -- left(c.text,50) as place from syscomments c inner join sysobjects o ON c.[id] = o.[id] where c.[text] like ''%' +@search+ '%'' order by cast(o.[name] as varchar(40)) ') fetch next from Curse into @databasenameend close Cursedeallocate Curseselect databasename ,name ,typefrom mydatabase.dbo.find_workingorder by databasename,nameset nocount offPut this code on a common database (or master) and put a proc in each db to call it so no matter what DB your in you can call it. It will output the database name, code object name and type. It is handy when you want to see what code touches a table that you are considering changing. Very cool code.
