Linked Server Dependencies

  • poedba@gmail.com

    SSC Enthusiast

    Points: 159

    Comments posted to this topic are about the item Linked Server Dependencies

  • Craig Purnell

    SSCrazy

    Points: 2660

    This code does not run - there are syntax errors.

  • ben.rosato

    SSCommitted

    Points: 1929

    Runs fine for me.

  • ben.rosato

    SSCommitted

    Points: 1929

    Just a couple of changes are required for this to work properly in my environment.

    I added a where clause on kerser2 WHERE ISREMOTE = '1'

    And I changed the search of syscomments to add a space before sp_srv_name and a "." after it. There were too many times that the server name was somewhere else in the procedure but not being used as a qualifier.

    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+'.%'')'

    That seemed to fix it up nicely.

    Thanks for this as I was in need of exactly this to clean up some security issues.

  • Phil Brammer

    Ten Centuries

    Points: 1135

    Since we're on the topic of llinked servers, the above where clause addition by ben.rosato should be WHERE ISREMOTE = 0, which means linked server (Ben may have needed ISREMOTE=0 for his environment). When using sys.servers, you can specify IS_LINKED = 1.

    With that said though, in the SQL Server 2005 section, the provided code by the OP is using the wrong tables ("wrong" only because they are depricated). Instead, the code should be using the new system views, sys.databases, sys.servers, sys.objects, and sys.sql_modules (syscomments). I also elected to use temp tables instead of perm.

    Thanks Andrew for posting this... It put me on the right track for what I needed as well.

    Below is my version of Andrew's code for SQL Server 2005, YMMV:

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

    Begin

    -- Get List of Databases

    declare kerser1 cursor fast_forward for

    select

    name

    from

    master.sys.databases (nolock)

    order by

    name

    if not exists (select * from tempdb.sys.objects where name like '#LinkedServerDependencies__%' and type = 'U')

    BEGIN

    create table #LinkedServerDependencies (

    [Database] varchar(100),

    [DependantObject] varchar(100),

    [LinkedServer] varchar(100))

    END

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

    select

    name

    from

    master.sys.servers (nolock)

    where

    is_linked = 1

    order by

    name

    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.objects (nolock)

    where object_id in (select object_id

    from '+@sp_db_name2+'.sys.sql_modules

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

    drop table #LinkedServerDependencies

    End

  • adam.cox-1026063

    Valued Member

    Points: 50

    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

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply