Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server Dependencies Expand / Collapse
Author
Message
Posted Wednesday, September 17, 2008 7:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:50 PM
Points: 60, Visits: 365
Comments posted to this topic are about the item Linked Server Dependencies
Post #571409
Posted Wednesday, October 15, 2008 7:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:21 PM
Points: 201, Visits: 501
This code does not run - there are syntax errors.


Post #586179
Posted Wednesday, October 15, 2008 8:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 361, Visits: 1,537
Runs fine for me.
Post #586225
Posted Wednesday, October 15, 2008 8:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 361, Visits: 1,537
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.
Post #586292
Posted Tuesday, December 30, 2008 3:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 9:50 AM
Points: 109, Visits: 601
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

Post #627701
Posted Wednesday, February 25, 2009 5:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 17, 2012 10:02 AM
Points: 2, Visits: 26
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
Post #664697
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse