November 17, 2003 at 11:01 pm
Hello all,
Does anyone know if I can find cross-database dependencies in SQL 7.0? It doesn't appear sp_depends will work for me. Thanks!
James
James Stover, McDBA
November 18, 2003 at 3:03 am
There won't be any foreign key dependencies, so I guess you're mostly thinking of sps and triggers. This will find references from the current DB to another named DB selected by you good self. It's a bit rough and ready but you get the idea.
declare @find varchar(100), @sp_prefix varchar(10)
------------------------------------------------
------------------------------------------------
set @find = 'XXXXXXX' --use name of target db
set @sp_prefix = '' --irrelevant for you purposes
------------------------------------------------
------------------------------------------------
set @sp_prefix = isnull(@sp_prefix,'') + '%'
set @find = '%' + @find + '%'
select distinct o.name,
replace(
replace(
replace(
replace(
replace(
replace(
substring(c1.text + isnull(c2.text,''),
case when patindex(@find, c1.text + isnull(c2.text,'')) < 101
then 1
else patindex(@find, c1.text + isnull(c2.text,'')) - 100
end,
patindex(@find, c1.text + isnull(c2.text,'')) + 150
)
,char(9),space(1))
,space(10),space(1))
,space(5),space(1))
,space(3),space(1))
,space(2),space(1))
,char(13) + char(10),' | ') text
from sysobjects o
join syscomments c1
on o.id = c1.id
left join syscomments c2
on c2.id = o.id
and c2.colid = c1.colid + 1
where o.xtype in ('P','TR') --stored procs and triggers
and o.name like @sp_prefix
and c1.text + isnull(c2.text,'') like @find
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply