• A good start. Thanks Andrew. Now here's my 'perfectionist' version (avec commentary)...

    Happy coding!

    /*

    * Usage: exec s0_get_linked_dependencies

    *

    * 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

    *

    *Change history:

    *Feb 25, 2009 by Adam Cox (http://adamcox.net)

    *- removed control character from original work causing invisible parse trouble

    *- added logic to skip offline databases

    *- reworked for efficiency (i.e. variable consolidation, re-order logic, etc)

    ***********************************************************************/

    use master

    go

    alter procedure s0_get_linked_dependencies

    as

    set nocount on

    declare @database varchar(100)

    declare @server varchar(100)

    declare @cmd varchar(8000)

    --Create Table to Store Results

    if not exists(select * from sysobjects (nolock) where name = 'LinkedServerDependencies' and xtype = 'U')

    create table LinkedServerDependencies (

    [Database] varchar(100),

    [DependantObject] varchar(100),

    [LinkedServer] varchar(100))

    else

    truncate table LinkedServerDependencies

    -- Check for SQL Version

    if (select @@version) like '%2000%'

    Begin

    -- Get List of Databases

    declare db_curs cursor read_only forward_only for

    select name

    from sysdatabases (nolock)

    --order by name

    open db_curs

    fetch next from db_curs into @database

    while @@fetch_status = 0

    Begin

    IF EXISTS(SELECT * FROM sysdatabases WHERE [name] = @database AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE')

    begin

    --Get List of Linked Servers

    declare svr_curs cursor read_only forward_only for

    select srvname

    from sysservers (nolock)

    --order by srvname

    open svr_curs

    fetch next from svr_curs into @server

    -- Populate Table of Dependencies

    while @@fetch_status = 0

    Begin

    set @cmd = 'insert into LinkedServerDependencies select ''' + @database

    set @cmd = @cmd + ''' as [Database], name as DependantObject, ''' + @server

    set @cmd = @cmd + ''' as LinkedServer from ' + @database

    set @cmd = @cmd + '..sysobjects where id in (select id from ' + @database

    set @cmd = @cmd + '..syscomments where text like ''%' + @server + '%'')'

    exec (@cmd)

    fetch next from svr_curs into @server

    End

    close svr_curs

    deallocate svr_curs

    end

    else

    print 'Database ' + @database + ' is off line'

    fetch next from db_curs into @database

    End

    close db_curs

    deallocate db_curs

    End

    -- Check for SQL Version

    if (select @@version) like '%2005%'

    Begin

    --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

    -- Get List of Databases

    declare db_curs cursor read_only forward_only for

    select name

    from sysdatabases (nolock)

    --order by name

    open db_curs

    fetch next from db_curs into @database

    while @@fetch_status = 0

    Begin

    IF EXISTS(SELECT * FROM sysdatabases WHERE [name] = @database AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE')

    begin

    --Get List of Linked Servers

    declare svr_curs cursor read_only forward_only for

    select srvname

    from sysservers (nolock)

    --order by srvname

    open svr_curs

    fetch next from svr_curs into @server

    -- Populate Table of Dependencies

    while @@fetch_status = 0

    Begin

    set @cmd = 'insert into LinkedServerDependencies select ''' + @database

    set @cmd = @cmd + ''' as [Database], name as DependantObject, ''' + @server

    set @cmd = @cmd + ''' as LinkedServer from ' + @database

    set @cmd = @cmd + '.sys.sysobjects (nolock) where id in (select id from '

    set @cmd = @cmd + @database + '.sys.syscomments where text like ''%'

    set @cmd = @cmd + @server

    set @cmd = @cmd + '%'')'

    exec (@cmd)

    fetch next from svr_curs into @server

    End

    close svr_curs

    deallocate svr_curs

    end

    else

    print 'Database ' + @database + ' is off line'

    fetch next from db_curs into @database

    End

    close db_curs

    deallocate db_curs

    End

    -- Return the results

    select LinkedServer, [Database], DependantObject

    from LinkedServerDependencies

    order by LinkedServer, [Database], DependantObject

    return 0

    go