View linked server dependencies sql server 2008

sql-server dependencies linked-server
Peuge picture Peuge · Jun 23, 2011 · Viewed 12.6k times · Source

Does any know how / if it is possible to view all tables/views/stored procedures that depend on a linked server in Sql Server 2008. Basically as if the context menu "View dependencies" was accessible for linked servers?

Any help much appreciated.

Thanks

Answer

gbn picture gbn · Jun 23, 2011

Search for it

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 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.

Related questions

How do I drop a column with object dependencies in SQL Server 2008?

The error message I'm obtaining when trying to drop a column: The object 'defEmptyString' is dependent on column 'fkKeywordRolleKontakt'. Msg 5074, Level 16, State 1, Line 43 ALTER TABLE DROP COLUMN fkKeywordRolleKontakt failed because one or more objects access this column. I have already …

Read More
How to Check all stored procedure is ok in sql server?

How to check all stored procedure is ok in sql server if I drop a table or fields?

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More