Situatie
Procedura de mai jos returneaza toate procedurile de pe serverul de sql care depind de linked-serverele definite pe acel server de sql. Procedura returneaza numele procedurii, baza de date pe care se afla acea procedura precum si linked serverul de care este dependenta.
Solutie
use master
go
alter procedure ProceduriDependenteDeLinkedServere_prc
as
Begin
set nocount on
if (select @@version) like ‘%2000%’
BEGIN
declare cursor1 cursor read_only forward_only for
select name from sysdatabases order by name
if not exists(select * from sysobjects (nolock) where name = ‘LinkedServerDependencies’ and xtype = ‘U’)
Begin
create table LinkedServerDependencies (
[Database] varchar(100),
DependantObject varchar(100),
LinkedServer varchar(100))
End
truncate table LinkedServerDependencies
open cursor1
declare @sp_db_name varchar(100)
declare @sp_srv_name varchar(100)
declare @sql varchar(8000)
fetch next from cursor1 into
@sp_db_name
while @@fetch_status = 0
Begin
declare cursor2 cursor read_only forward_only for
select srvname from sysservers order by srvname
open cursor2
fetch next from cursor2 into @sp_srv_name
while @@fetch_status = 0
Begin
set @sql = ‘insert into LinkedServerDependencies select ”’+@sp_db_name+”’ as [Database], name as DependantObject, ”’+@sp_srv_name+”’ as LinkedServer from ‘+@sp_db_name+’..sysobjects where id in (select id from ‘+@sp_db_name+’..syscomments where text like ”%’+@sp_srv_name+’%”)’
exec (@sql)
fetch next from cursor2 into @sp_srv_name
End
close cursor2
deallocate cursor2
fetch next from cursor1 into @sp_db_name
End
close cursor1
deallocate cursor1
– Return the results
select * from LinkedServerDependencies
End
ELSE
Begin
declare cursor1 cursor read_only forward_only for
select name from sysdatabases order by name
if not exists(select * from sysobjects (nolock) where name = ‘LinkedServerDependencies’ and xtype = ‘U’)
Begin
create table LinkedServerDependencies (
[Database] varchar(100),
[DependantObject] varchar(100),
[LinkedServer] varchar(100))
End
truncate table LinkedServerDependencies
open cursor1
declare @sp_db_name2 varchar(100)
declare @sp_srv_name2 varchar(100)
declare @sql2 varchar(8000)
fetch next from cursor1 into @sp_db_name2
while @@fetch_status = 0
Begin
declare cursor2 cursor read_only forward_only for
select srvname from sysservers order by srvname
open cursor2
fetch next from cursor2 into @sp_srv_name2
while @@fetch_status = 0
Begin
set @sql2 = ‘insert into LinkedServerDependencies select ”’+@sp_db_name2+”’ as [Database], name as DependantObject, ”’+@sp_srv_name2+”’ as LinkedServer from ‘+@sp_db_name2+’.sys.sysobjects (nolock) where id in (select id from ‘+@sp_db_name2+’.sys.syscomments where text like ”%’+@sp_srv_name2+’%”)’
exec (@sql2)
fetch next from cursor2 into @sp_srv_name2
End
close cursor2
deallocate cursor2
fetch next from cursor1 into @sp_db_name2
End
close cursor1
deallocate cursor1
select * from LinkedServerDependencies
End
End
Leave A Comment?