Find Anything! | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Find Anything!

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 ”%’ [email protected]+ ‘%” 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.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |