sqlschool.gr logo

articles

Articles of SQLschool.gr Team

How to discover nested views

Antonios Chatzipavlis
Friday 03 April 2020

Για όσους θυμούνται από το δημοτικό το Ανθολόγιο και το κείμενο του Δ. Ψαθά, "Η τσάντα και το τσαντάκι" που υπήρχε σε αυτό πιθανώς θα γελάσουν, και καλά θα κάνουν

Δυστυχώς οι περιπτώσεις των nested views δεν δίνουν το ίδιο χαμόγελο, το αντίθετο θα έλεγα. Ειδικά αυτές που με την λογική της μπαμπούσκας γίνονται τάφρος χωρίς τέλος.

Επειδή πάντα είναι καλό να ξέρεις τι έχεις να αντιμετωπίσεις στο θέμα αυτό παρακάτω έχω δημιουργήσει ένα recursive CTE που φτάνει μέχρι 32 επίπεδα και που αναλύει τα views βλέποντας τα dependencies τους. Αν υπάρχουν παραπάνω από 32 τότε κάτι πάει πραγματικά λάθος.

with nvfind 
as (

    select distinct
            d.referencing_id,
            d.referenced_id,
            s.name as schemaname,
            o.name as viewname,
            convert(nvarchar(2000), s.name+'.'+o.name) collate database_default as viewnestinglevel,
            o.type_desc,
            0 as level
    from sys.sql_expression_dependencies as d
    inner join sys.objects as o on o.object_id = d.referencing_id and o.type_desc ='view'
    inner join sys.schemas as s on s.schema_id = o.schema_id
    left outer join sys.objects o2 on o2.object_id = d.referenced_id and o2.type_desc in ('view')
    where o2.object_id is null 
 
    union all

    select
            d.referencing_id,
            d.referenced_id,
            s.name as sch,
            o.name as viewname,
            convert(nvarchar(2000),  s.name+'.'+o.name + N'->' + nvfind.viewnestinglevel) collate database_default,
            o.type_desc,
            level + 1 as level
    from sys.sql_expression_dependencies as d
    inner join sys.objects o on o.object_id = d.referencing_id and o.type_desc ='view'
    inner join sys.schemas as s on s.schema_id = o.schema_id
    inner join nvfind on d.referenced_id = nvfind.referencing_id
), v as
(
select  
        schemaname+'.'+viewname as view_name, 
        viewnestinglevel as view_nested_views,
        level,
        ROW_NUMBER() over (partition by schemaname+'.'+viewname order by level desc) as  rr
from nvfind
where level > 0
)
select view_name,view_nested_views,level
from v
where rr = 1
order by level desc, view_name
option (maxrecursion 32)

Ένα παράδειγμα για να δούμε το αποτέλεσμα είναι το παρακάτω

create view dbo.V1 as
select custid,contactname,city,country from sales.Customers
go

create view dbo.V2 as 
select v1.*, o.orderid,o.orderdate from dbo.v1 
inner join sales.Orders as o on v1.custid = o.custid
go

create view dbo.V3 as 
select v2.*, d.productid,d.qty,d.unitprice from dbo.v2 
inner join sales.OrderDetails as d on v2.orderid = d.orderid
go

view_name view_nested_views      level
--------- ---------------------- -----
dbo.V3    dbo.V3->dbo.V2->dbo.V1 2
dbo.V2    dbo.V2->dbo.V1         1



Στο οποίο βλέπουμε ότι η V3 χρησιμοποιεί την V2 η οποία με την σειρά της V1 και αντίστοιχα η V2 χρησιμοποιεί την V1.

Με αυτό τον τρόπο εύκολα και γρήγορα ξέρουμε πλέον ποια είναι τα view αυτά που πρέπει άμεσα να ασχοληθούμε και να αλλάξουμε



//Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn

sqlschool.gr © 2010-2025 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.