Sometime ago, I needed to find a particular string somewhere in my database but didn't want to spend hours/days searching for it. I found this little procedure in sqlserver magazine's website. Very useful:
USE [DATABASE_NAME] --this should change to whatever your database name is
GO
/****** Object: StoredProcedure [dbo].[Find] Script Date: 02/09/2009 09:46:53 ******/
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: Michael F. Berry
--
-- Modified Dte:1/25/2007
--
-- Modified Rsn:Make it put out into one main recordset for clarity
--*****************************************************************
ALTER 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(200),ObjectName varchar(70), ObjectType varchar(30))
DECLARE Curse CURSOR local fast_forward
FOR
SELECT
name
FROM
master.dbo.sysdatabases
WHERE
name not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW')
OPEN Curse
FETCH next FROM Curse INTO @databasename
WHILE @@fetch_status = 0
BEGIN
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 @strSQL
FETCH next FROM Curse INTO @databasename
END
CLOSE Curse
DEALLOCATE Curse
SELECT
DatabaseName,
ObjectName,
ObjectType
FROM #FIND_WORKING
ORDER BY DatabaseName,ObjectName
SET nocount OFF
Enjoy!