September 17, 2008 at 7:31 pm
Comments posted to this topic are about the item Linked Server Dependencies
October 15, 2008 at 7:24 am
This code does not run - there are syntax errors.
October 15, 2008 at 8:06 am
Runs fine for me.
October 15, 2008 at 8:53 am
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.
December 30, 2008 at 3:23 pm
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
February 25, 2009 at 5:06 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply