In SQL Server 2008, for a view in a given database, I can get the tables in other databases on which the view depends by executing:
select distinct referenced_database_name, referenced_schema_name, referenced_entity_name
from sys.dm_sql_referenced_entities ('dbo.NameOfView', 'OBJECT')
where referenced_database_name is not null
However, sys.dm_sql_referenced_entities is not available in SQL Server 2005. Is there a way to obtain, from the metadata, a list of tables in other databases on which a view depends?
From Aaron Bertrand's vaguely related blog entry:
The system table sysdepends does not maintain dependency information
for any objects located outside of the local database. This means
that if you want to check the validity of objects with three- or
four-part names in SQL Server 2005 or earlier, you are going to have
to manually parse the definitions of all of your objects.
He goes on to recommend a parser from here - but it's still a nasty job on 2005 I'm afraid.
You can use sys.sql_dependencies.
It will give you per column dependencies though so you'd have to unique the major id to get the table.
select distinct object_name(referenced_major_id)
from sys.sql_dependencies
where object_id = object_id('viewName')
As far as getting the results from other databases, that will be tricky as I don't know how the data is stored cross-database and can't test it as I don't have a SQL Server 2005 install readily available to see. In SQL Server 2008+, the new view is sys.sql_expression_dependencies which is what drives sys.dm_sql_referenced_entities that you mentioned.
I'll do more digging to see if I can figure any more out.
I found something that might be of interest to you.
Try sp_depends. BOL states that you can derive the dependencies of the view using this sproc in SQL Server 2005. BOL Link
Yes Zhenny, we can achieve this from sqlserver2008+ edition. onwards using below query to list all the objects that are used from the other databases also.
select referenced_entity_name,referenced_database_name
from sys.sql_expression_dependencies
where referencing_id = object_id('SCHEMA.OBJECTNAME')
