Find all references to a database

Is there a way to scan all procedures, functions and triggers in an SQL
server for references to a specific database?
Thanks
Tom G.The following technique isn't perfect but will identify most objects. You
could also script the textual objects to a file and use a find command.
USE MyDatabase
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%SomeDatabase%'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Groszko" <newscorrespondent@.charter.net> wrote in message
news:emfU1rr1DHA.1184@.TK2MSFTNGP10.phx.gbl...
> Is there a way to scan all procedures, functions and triggers in an SQL
> server for references to a specific database?
> Thanks
> Tom G.
>|||The solution I have come up with is:
Create a table like this:
create table PAFCONVERSION (
DBNAME sysname not null,
OBJECTNAME sysname not null,
OBJECTYPE char(2) null,
constraint PK_PAFCONVERSION primary key (DBNAME, OBJECTNAME)
)
Run this in query analyzer, copy the output pane to the execution pane and
run it.
SET NOCOUNT ON
USE master
go
DECLARE DBCURSOR CURSOR FAST_FORWARD FOR
SELECT NAME FROM SYSDATABASES ORDER BY NAME FOR READ ONLY
DECLARE @.DBNAME SYSNAME
OPEN DBCURSOR
FETCH DBCURSOR INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE @.DBPROCESS varchar(8000)
SET @.DBPROCESS = 'USE ' + @.DBNAME + CHAR(10) + 'GO' + CHAR(10)
+ 'INSERT SDRMONITOR.DBO.PAFCONVERSION (DBNAME, OBJECTNAME, OBJECTYPE)' +
CHAR(10)
+ 'SELECT DISTINCT ''' + @.DBNAME + ''', SYSOBJECTS.NAME, SYSOBJECTS.XTYPE'
+ CHAR(10)
+ 'FROM syscomments' + CHAR(10)
+ 'JOIN SYSOBJECTS ON (SYSCOMMENTS.ID = SYSOBJECTS.ID)' + CHAR(10)
+ 'where text like (''%what you are looking for%'') ' + CHAR(10)
+ 'ORDER BY SYSOBJECTS.NAME, SYSOBJECTS.XTYPE' + CHAR(10)
+ 'GO' + CHAR(10)
SELECT @.DBPROCESS
FETCH DBCURSOR INTO @.DBNAME
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
GO
"Tom Groszko" <newscorrespondent@.charter.net> wrote in message
news:emfU1rr1DHA.1184@.TK2MSFTNGP10.phx.gbl...
> Is there a way to scan all procedures, functions and triggers in an SQL
> server for references to a specific database?
> Thanks
> Tom G.
>