go backarticles

Articles of SQLschool.gr Team

How to discover nested views

Antonios Chatzipavlis

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

Δυστυχώς οι περιπτώσεις των 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 is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Relative Articles

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

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