Find anything in a database

This proc will allow you to search your entire SQL server for code. This means you can look for anything referecing a table so you can replace it with an indexed view or whatever. It is very fast and very handy. Run it in text output mode and print it off.. It tells you where the code was found (in what object - sp, view, UDF, etc.) and reminds you of your search criteria so you can review the document later. Enjoy!

USE [EBS_Common]
GO
/****** Object: StoredProcedure [dbo].[Find] Script Date: 04/18/2007 11:26:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--*****************************************************************
-- 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:
--
-- Modified Dte:
--
-- Modified Rsn:
--*****************************************************************

CREATE Procedure [dbo].[Find]

(
@search varchar(150)
)

as

set nocount on

declare @databasename as varchar(200)

declare Curse cursor local fast_forward
for
select distinct
name
from
master.dbo.sysdatabases
where
name not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW','indataDB_main','IndataDB_MarketValues','IndataDB_PriorMonth','PartnerControl','PartnerData')
open Curse


fetch next from Curse into @databasename

while @@fetch_status = 0
begin

exec ('use ' + @databasename + '
select cast(''Searching for "' + @SEARCH + '" in : ' + @databasename + '********'' as varchar(60))



select distinct
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 @databasename
end
close Curse
deallocate Curse

set nocount off


Michael
MCDBA

"The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

Luis,
I changed it to use a temp table. This will be in SQL Server Magazine this Aug or Sep.USE [DBA]
GO/****** Object: StoredProcedure [dbo].[Find] Script Date: 04/29/2008 12:48:33 ******/SET
ANSI_NULLS ONGOSET
QUOTED_IDENTIFIER ONGO--*****************************************************************
-- 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 PROC [dbo].[Find]
(@search varchar
(150)
)AS
SET nocount ON
DECLARE
@strSQL as NVARCHAR(800)DECLARE
@databasename as varchar(200)SELECT 'Searching For: '''+ @search + '''' as Current_Search CREATE
TABLE #FIND_WORKING (DatabaseName varchar(15),ObjectName varchar(70), ObjectType varchar(30))DECLARE
Curse CURSOR local fast_forwardFOR
SELECT nameFROMmaster
.dbo.sysdatabases WHEREname
not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW') OPEN Curse FETCH
next FROM Curse INTO @databasenameWHILE
@@fetch_status = 0BEGIN
SET @strSQL= 'use ' + @databasename + '
insert into #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))
'EXEC
dbo.sp_executesql @strSQLfetch
next from Curse into @databasenameend
close
Cursedeallocate
Curseselect DatabaseName
,ObjectName
,ObjectTypefrom
#FIND_WORKINGorder
by DatabaseName,ObjectNameset
nocount off