I picked up a number a couple of questions from Thursday night's TechNet Road show in London including a couple on dependencies in SQL Server 2008. So here's the answers...
Firstly there are two sorts of dependencies:
Dependencies are updated when a referring object is created and rely on names so if you want to rely on this then you need to use proper four part names i.e. MyServer.MyDatabase.MyTable.MyColumn.
Note that you can get dependencies across linked servers but this will only work with the four part naming convention and not EXEC ('…') AT linked_srv1
The next question I got asked was how synonyms work with dependencies. so here's an example:
-- Create a synonym for the Product table in AdventureWorks. USE AdventureWorksDW CREATE SYNONYM dbo.MyProduct FOR AdventureWorks.Production.Product; GO
-- check the dependency for MyView SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name ,referenced_server_name AS server_name ,referenced_database_name AS database_name ,referenced_schema_name AS schema_name , referenced_entity_name FROM sys.sql_expression_dependencies WHERE referencing_id = OBJECT_ID(N'dbo.MyProc1');
All this shows is the dependency on the synonym
If a view is created against the table directly i.e.
CREATE VIEW dbo.MyView AS SELECT Name,ProductNumber FROM AdventureWorks.Production.Product;
..then the dependency reports correctly.
I have to say I don't think that's a great story and you get the same information back from the management console if you right click on an object and select view dependencies.
I got back from vacation ( more here if you want to read about where we go and what we do every other