October 19, 2015 at 9:26 am
Hello, Can anyone provide me with a script to get the list of views in a database, involving tables from other databases??...appreciate your help..am using SQL server 2014
October 19, 2015 at 9:40 am
There is a system table called sys.sql_expression_dependencies which contains references between objects, for example when a view or stored procedure references tables. If referenced_database_name is not null, then the referenced object is from another database, and if referenced_server_name is not null, then the object is from another server (ie: referenced via a linked server).
SELECT object_name(referencing_id)object_name
, referenced_server_name
, referenced_database_name
, referenced_schema_name
, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name is not null;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 19, 2015 at 10:07 am
Thanks much Eric...this was helpful..I just made slight changes to suit my requirement
select
object_name(referencing_id) as ViewName
,o.type_desc
,referenced_database_name + '.' + referenced_schema_name + '.' + referenced_entity_name as ReferencedTable
--,*
from sys.sql_expression_dependencies d
inner join sys.objects o on o.object_id = d.referencing_id
where referenced_database_name is not null and is_ambiguous = 0 and type_desc = 'VIEW'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply