February 15, 2011 at 8:11 am
I have couple of proc 2 proc in my db which have a linked server and use data from a linked server,
On our server instance there are other teams who have databases on the same instance.
and there is a close bonding between the two applications,
We now want to decouple,
There are few hundred proc in this current DB(Z) which has links to DB(A) and DB(C)
I want to do some thing like this
Use DBZ
go
--Find all the objects which have external dependencies either via Linked server or or via cross database link using example DBA.dbo.tablename
is there an easy way to figure this out?
Looked at the sys.sysdepends table
select object_name(id), object_name(depid),* from sys.sysdepends where
id in ( select distinct object_id from sys.sql_modules )
where depdbid <> 0
but it did not work for me any idea on where i may be going wrong? or am i off the track completely.
Kind Regards
Vinay.
Regards
Vinay
February 15, 2011 at 8:38 am
RedGate has a product that will do that for you.
Without that, I expect it will be a matter of querying things like:
select *
from sys.sql_modules
inner join sys.servers
on definition like '%' + sys.servers.name + '%';
That'll get you started, but will be more work than the RedGate tool.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2011 at 9:13 am
Thank you i did change it for the databases.
select *
from sys.sql_modules
inner join sys.servers
on definition like '%' + sys.servers.name + '%';
select *
from sys.sql_modules
inner join sys.databases
on definition like '%' + sys.databases.name + '%';
Thank you.
Regards
Vinay
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply