Forwarding: Querying the underlying tables for all references in a view using SQL commands

Turn from:querying the underlying tables for all references in a view using SQL commands
Using SQL commands to query all referenced base tables in a view before having written how to exploit all referenced tables in SQL query view this method does not identify all the underlying tables in the view, and if there are nested views in the view, there is a problem because the catalog view Sys.sql_ Dependencies does not contain all the referencing entities. The Sys.sql_expression_dependencies view that was introduced in sql2008 and later releases solves this problem, so it re-writes the section SQL to query the underlying table for all references in the view, including the underlying tables in the nested view. What does this have to do with each individual's usefulness, I am used to estimate and optimize the efficiency of the view, if there are too many underlying tables referenced in the view, will it affect performance to some extent?

The SQL statement for the query is as follows:
DECLARE @v_name varchar (50)
Set @v_name = ' V_view '--the view name that needs to be queried

DECLARE @t_temp_referenced table
(
Tname varchar (50),
Ttype varchar (10),
Tlevel int
)

Insert into @t_temp_referenced (tname,ttype,tlevel)
Select a.referenced_entity_name,b.xtype,0 from
(
Select distinct Referenced_entity_name from sys.sql_expression_dependencies
where referencing_id = object_id (@v_name) and [referenced_class] = 1 and [is_ambiguous] = 0
) A
Left join Sys.sysobjects B on a.referenced_entity_name=b.name

DECLARE @name varchar (50)
DECLARE @level int
Set @level =0

while (select COUNT (1) from @t_temp_referenced where ttype= ' and [email protected]) >0
Begin
Declare result cursor local for select Tname from @t_temp_referenced where ttype= ' and [email protected]
Open result
FETCH NEXT from the result into @name
While @ @fetch_status =0
Begin
Insert into @t_temp_referenced (tname,ttype,tlevel)
Select A.referenced_entity_name,b.xtype, @level +1 from
(
Select distinct Referenced_entity_name from sys.sql_expression_dependencies
where referencing_id = object_id (@name) and [referenced_class] = 1 and [is_ambiguous] = 0
) A
Left join Sys.sysobjects B on a.referenced_entity_name=b.name
Where NOT EXISTS (select 1 from @t_temp_referenced t1
where T1.tname=a.referenced_entity_name and T1.ttype=b.xtype and [email protected]+1)

FETCH NEXT from the result into @name
End

Close result
deallocate result

Set @[email protected]+1
End

Select distinct Tname from @t_temp_referenced
where ttype= ' U '

Note:sys.sql_expression_dependencies View only sql2008 and above, sql2005 is not .
The instructions for copying the sys.sql_expression_dependencies view are as follows:

Column Name	Data Type	Description
referencing_id	Int	The ID of the referencing entity. Cannot be Null.
referencing_minor_id	Int	The column ID when the referencing entity is a column; otherwise 0. Cannot be Null.
Referencing_class	tinyint	The class that references the entity. 1 = object or column 12 = Database DDL Trigger 13 = Server DDL Trigger Cannot be Null.
Referencing_class_desc	nvarchar (60)	A description of the class that references the entity. Object_or_column Database_ddl_trigger Server_ddl_trigger Cannot be Null.
Is_schema_bound_reference	Bit	1 = The referenced entity is bound to the schema. 0 = The referenced entity is not bound to the schema. Cannot be Null.
Referenced_class	tinyint	The class of the referenced entity. 1 = object or column 6 = Type Ten = XML schema collection 21 = Partition function Cannot be Null.
Referenced_class_desc	nvarchar (60)	A description of the class of the referenced entity. Object_or_column TYPE Xml_schema_collection Partition_function Cannot be Null.
Referenced_server_name	sysname	The name of the server for the referenced entity. This column is populated with cross-server dependencies that are generated by specifying a valid four-part name. Information about names that are composed of multiple parts. For non-schema-bound entities, this column is NULL if the entity is referenced without specifying a four-part name. For schema-bound entities, this column is NULL because they must be in the same database and can only be defined with a two-part (Schema.object) name.
Referenced_database_name	sysname	The name of the database for the referenced entity. This column is populated for cross-database or cross-server references that are generated by specifying a valid name consisting of three parts or four parts. For non-schema-bound references, this column is NULL when specified with a part or two-part name. For schema-bound entities, this column is NULL because they must be in the same database and can only be defined with a two-part (Schema.object) name.
Referenced_schema_name	sysname	The schema to which the referenced entity belongs. For non-schema-bound references, this column is NULL if the entity is referenced without specifying a schema name. For schema-bound references, this column is never NULL because a two-part name must be used to define and reference the entity bound to the schema.
Referenced_entity_name	sysname	The name of the referenced entity. Cannot be Null.
referenced_id	Int	The ID of the referenced entity. For cross-server and cross-database references, this column is always NULL. For references within the database, NULL if the ID cannot be determined. For non-schema-bound references, the ID cannot be resolved under the following circumstances: The referenced entity does not exist in the database. The schema of the referenced entity relies on the caller's schema and resolves at run time. In this case, the is_caller_dependent is set to 1. This column will never be NULL for a schema-bound reference.
referenced_minor_id	Int	The ID of the column referenced when the entity is a column; otherwise 0. Cannot be Null. The referenced entity is a column when the column is identified by name in the referencing entity, or when a parent entity is used in a SELECT * statement.
Is_caller_dependent	Bit	Indicates that the schema binding of the referenced entity occurs at run time, so the resolution of the entity ID depends on the caller's schema. This behavior occurs when the referenced entity is a stored procedure, an extended stored procedure, or a non-schema-bound user-defined function that is called in an EXECUTE statement. 1 = The referenced entity relies on the caller and resolves at run time. In this case, referenced_id is NULL. 0 = The referenced entity ID is not dependent on the caller. For schema-bound references, cross-database and cross-server references that explicitly specify the schema name, are always 0. For example, a reference to an entity that is formatted as EXEC MyDatabase.MySchema.MyProc is not dependent on the caller. However, the format is EXEC MyDatabase. The reference to the MYPROC depends on the caller.
Is_ambiguous	Bit	Indicates that the reference is an ambiguous reference and can be resolved at run time to a user-defined function, user-defined type (UDT), or an XQuery reference to a column of XML type. For example, assume that the statement SELECT Sales.getorder () from sales.mysales is defined in a stored procedure. Before executing a stored procedure, it is not known whether Sales.getorder () is a user-defined function in the Sales schema or with a method named GetOrder (), a column of type UDT, and the name sales. 1 = ambiguous reference. 0 = The reference is unambiguous, or the entity can be bound successfully when the view is invoked. A reference that is bound to a schema is always 0.