SQLServerCentral.com / Article Discussions / Article Discussions by Author / Discuss content posted by Andrew Miller / Linked Server Dependencies / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 16:04:44 GMT20RE: Linked Server Dependencieshttp://www.sqlservercentral.com/Forums/Topic571409-1385-1.aspxA 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 mastergoalter procedure s0_get_linked_dependenciesas 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 0goWed, 25 Feb 2009 17:06:09 GMTadam.cox-1026063RE: Linked Server Dependencieshttp://www.sqlservercentral.com/Forums/Topic571409-1385-1.aspxSince 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:[code]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 #LinkedServerDependenciesEnd[/code]Tue, 30 Dec 2008 15:23:29 GMTPhil BrammerRE: Linked Server Dependencieshttp://www.sqlservercentral.com/Forums/Topic571409-1385-1.aspxJust 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.Wed, 15 Oct 2008 08:53:00 GMTben.rosatoRE: Linked Server Dependencieshttp://www.sqlservercentral.com/Forums/Topic571409-1385-1.aspxRuns fine for me.Wed, 15 Oct 2008 08:06:31 GMTben.rosatoRE: Linked Server Dependencieshttp://www.sqlservercentral.com/Forums/Topic571409-1385-1.aspxThis code does not run - there are syntax errors.Wed, 15 Oct 2008 07:24:56 GMTCraig PurnellLinked Server Dependencieshttp://www.sqlservercentral.com/Forums/Topic571409-1385-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Linked+Server/64276/">Linked Server Dependencies</A>[/B]Wed, 17 Sep 2008 19:31:22 GMTAndrew Miller-489894