Proceduri dependente de linked server

Configurare noua (How To)

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

Tip solutie

Permanent

Voteaza

(9 din 17 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?