CODEWITHSUNDEEP
sql-server
user37793
user37793

Reputation: 31

SQL Server: list of views referencing other views

SQL Server: I would like to produce a list of views that reference other views. The reason for this so that views can be created int the proper sequence, not having to resort to obscure view names to accomplish this. Oracle provides this solution: select * from USER_DEPENDENCIES where type = 'VIEW' and referenced_type = 'VIEW' Which produces the desired result. I have not found anything similar in SQL Server; I have been referencing INFORMATION_SCHEMA and its various components without success.

Upvotes: 0

Views: 929

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88851

Here's a DMV query to get you started.

use tempdb
drop table if exists t

go
create table t(id int, a int, b int)
go
create or alter view v1 as select id, a from t
go
create or alter view v2 as select * from v1
go

select schema_name(v.schema_id) view_schema_name, v.name view_name, d.referenced_entity_name 
from 
sys.views v
join sys.sql_expression_dependencies d
  on v.object_id = d.referencing_id

which outputs

  view_schema_name      view_name      referenced_entity_name
--------------------- -------------- ---------------------------
dbo                   v1                 t
dbo                   v2                 v1

Upvotes: 1

George Mastros
George Mastros

Reputation: 24498

Take a look at sp_depends. 

sp_depends 'YourViewNameHere'

You will get 2 recordsets. The first shows the objects that the view depends on, and the second shows the objects that depend on the view.

Upvotes: 0

Related Questions