View sql server 2008 dependent server dependencies

Question

Does anyone know how / if you can view all tables / views / stored procedures that depend on the linked server on Sql Server 2008. Basically, as if the context menu "View Dependencies" is available for linked servers?

Any help is greatly appreciated.

thanks

Answer 1

Find him

SELECT OBJECT_NAME(object_id), * FROM sys.sql_modules WHERE definition LIKE '%myLinkedServer%' 

Or use the free Red gate SQL Search to do the same with the GUI.

There is no table or function that tracks dependencies between related server-side objects at the server level and database-level objects

Note. INFORMATION_SCHEMA views and legacy systems crop the definition, so it is not reliable to find the definition.

Answer 2

Following @Mitch Wheat's suggestion, here are some sql to run the @gbn response for all db on the server. Maybe it will be a little for someone.

 USE Master; GO IF OBJECT_ID('tempdb..#Deps') IS NOT NULL BEGIN DROP TABLE #Deps END CREATE TABLE #Deps ( [ServerName] [VARCHAR](500) NOT NULL, [DatabaseName] [VARCHAR](500) NOT NULL, [SchemaName] [VARCHAR](500) NOT NULL, [ObjectName] [VARCHAR](MAX) NULL, [ObjectId] [INT] NOT NULL, [ObjectType] [VARCHAR](500) NOT NULL, [DependsOnLinkedServer] [VARCHAR](500) NOT NULL, [definition] [VARCHAR](MAX) NULL ) IF OBJECT_ID('tempdb..#Queries') IS NOT NULL BEGIN DROP TABLE #Queries END SELECT REPLACE('INSERT INTO #Deps ( [ServerName] ,[DatabaseName] ,[SchemaName] ,[ObjectName] ,[ObjectType] ,[ObjectId] ,[DependsOnLinkedServer] ,[definition] ) SELECT @@SERVERNAME, ''?'' AS DatabaseName, s.name AS SchemaName, o.name AS ObjectName, o.type_desc AS ObjectType, m.object_id AS ObjectId, ''' + srv.name + ''' AS DependsOnLinkedServer, m.definition FROM [?].sys.sql_modules m LEFT OUTER JOIN [?].sys.objects o ON m.object_id = o.object_id LEFT OUTER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id WHERE definition LIKE ''%' + srv.name + '%''', CHAR(13) + CHAR(10), '') AS Query INTO #Queries FROM sys.servers srv; GO DECLARE @Query AS VARCHAR(MAX) DECLARE LinkedServerCursor CURSOR FAST_FORWARD FOR SELECT Query FROM #Queries OPEN LinkedServerCursor FETCH NEXT FROM LinkedServerCursor INTO @Query; WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE master.sys.sp_MSforeachdb @Query FETCH NEXT FROM LinkedServerCursor INTO @Query; END CLOSE LinkedServerCursor; DEALLOCATE LinkedServerCursor; GO SELECT ServerName, DatabaseName, ObjectName, '[' + ServerName + '].[' + DatabaseName + '].[' + SchemaName + '].[' + ObjectName + ']' AS QualifiedObjectName, DependsOnLinkedServer, ObjectType FROM #Deps ORDER BY ServerName, DatabaseName, ObjectName 

Answer 3

I would recommend changing the middle part of the request as shown below to make sure that only when it is used in the request, you get hit. If not, you will get many false positives whenever the server name is mentioned.

 WHERE definition LIKE ''%\[' + srv.name + '\].%'' or definition LIKE ''%' + srv.name + '.%''', CHAR(13) + CHAR(10), '') AS Query