List of all different database tables used in views-CodePudding

I need to find all the different database tables used in views.
I tried information_schema.view_table_usage.
I got results for current database.
But tables associated with different database are not fetching.
Please guide.

CodePudding user response：

Don't use information_schema (here's why) or sysdepends (here's why).

SELECT v.name, 
  ed.referenced_database_name,
  ed.referenced_schema_name,
  ed.referenced_entity_name
FROM sys.views AS v
INNER JOIN sys.sql_expression_dependencies AS ed
ON v.[object_id] = ed.referencing_id;

If you only want things in another database, add:

WHERE ed.referenced_database_name IS NOT NULL;

CodePudding user response：

You can use the sys tables sys.objects and sys.sysdepends to retrieve the information you as follows

--Replace 'YOU VIEW NAME' by the view you want

--1) using old sys tables
select distinct(ov.name),ov.xtype from sysobjects o 
inner join sysdepends d on o.id=d.id 
inner join sysobjects ov on d.depid=ov.id
where o.name='YOUR VIEW NAME'


--2) using new sys.table

select distinct(ov.name),ov.type from sys.objects o 
inner join sys.sysdepends d on o.object_id=d.id 
inner join sys.objects ov on d.depid=ov.object_id
where o.name='YOUR VIEW NAME'