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