Linked Server Dependencies

use master
go

create procedure s0_get_linked_dependencies
as
/***********************************************************************\
    This Proc is written for either SQL 2000 or SQL 2005 to return
    a list of objects that are dependent on Linked Servers. This
    proc checks all Databases and Objects on a Server.

    Author:    Andrew C Miller
    Date:    09/05/2008

    Usage:    exec s0_get_linked_dependencies
\***********************************************************************/        

Begin
set nocount on
--    Check for SQL Version
if (select @@version) like '%2000%'
Begin
    --    Get List of Databases
    declare kerser1 cursor read_only forward_only for
    select
        name
    from 
        sysdatabases (nolock)
    order by
        name

    --Create Table to Store Results
    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 kerser1

    declare @sp_db_name varchar(100)
    declare @sp_srv_name varchar(100)
    declare @sql varchar(8000)

    fetch next from kerser1 into
        @sp_db_name

    while @@fetch_status = 0
    Begin
        --Get List of Linked Servers
        declare kerser2 cursor read_only forward_only for
        select 
            srvname 
        from 
            sysservers (nolock)
        order by srvname

        open kerser2

        fetch next from kerser2 into
            @sp_srv_name

        --    Populate Table of Dependencies
        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 kerser2 into
                @sp_srv_name
        End

        close kerser2
        deallocate kerser2

        fetch next from kerser1 into
            @sp_db_name
    End

    close kerser1
    deallocate kerser1

    --    Return the results
    select * from LinkedServerDependencies
End
--    Check for SQL Version
if (select @@version) like '%2005%'
Begin
    --    Get List of Databases
    declare kerser1 cursor read_only forward_only for
    select
        name
    from 
        sysdatabases (nolock)
    order by
        name

    --Create Table to Store Results
    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 kerser1

    declare @sp_db_name2 varchar(100)
    declare @sp_srv_name2 varchar(100)
    declare @sql2 varchar(8000)

    fetch next from kerser1 into
        @sp_db_name2

    while @@fetch_status = 0
    Begin
        --Get List of Linked Servers
        declare kerser2 cursor read_only forward_only for
        select 
            srvname 
        from 
            sysservers (nolock)
        order by srvname

        open kerser2

        fetch next from kerser2 into
            @sp_srv_name2

        --    Populate Table of Dependencies
        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 kerser2 into
                @sp_srv_name2
        End

        close kerser2
        deallocate kerser2

        fetch next from kerser1 into
            @sp_db_name2
    End

    close kerser1
    deallocate kerser1

    --    Return the results
    select * from LinkedServerDependencies
End
End