View linked server dependencies sql server 2008

Search for it

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

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

There is no table or feature that tracks dependencies between the server-level linked server objects and database-level objects

Note: INFORMATION_SCHEMA views and the legacy syscomments truncate the definition so are unreliable for definition searches.

Following @Mitch Wheat's suggestion, here's some sql to run @gbn's answer for all db's on the server. Maybe this will a bit of time for someone.

USE Master;GOIF OBJECT_ID('tempdb..#Deps') IS NOT NULL  BEGIN    DROP TABLE #Deps  ENDCREATE 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  ENDSELECT  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 QueryINTO  #QueriesFROM  sys.servers srv;GODECLARE @Query AS VARCHAR(MAX)DECLARE LinkedServerCursor CURSOR FAST_FORWARDFOR  SELECT    Query  FROM    #QueriesOPEN LinkedServerCursorFETCH NEXT FROM LinkedServerCursor INTO @Query;WHILE @@FETCH_STATUS = 0  BEGIN      EXECUTE master.sys.sp_MSforeachdb @Query    FETCH NEXT FROM LinkedServerCursor INTO @Query;  ENDCLOSE LinkedServerCursor;DEALLOCATE LinkedServerCursor; GOSELECT  ServerName,  DatabaseName,  ObjectName,  '[' + ServerName + '].[' + DatabaseName + '].[' + SchemaName + '].[' + ObjectName + ']' AS QualifiedObjectName,  DependsOnLinkedServer,  ObjectType  FROM  #DepsORDER BY  ServerName,  DatabaseName,  ObjectName

I would recommend changing the middle part of the query to be as below to ensure only when it is used in a query you get a hit.If not you will get a lot of false positives whenever the servername is mentioned.

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