escartefigue

La requête ci-dessous permet d'identifier les impact de la destruction d'une table sur les objets dépendants (table spaces, vues, triggers...)

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
 
-- analyse d'impact d'un drop de table(s)                                      
   set current_schema = 'SYSIBM'                                               
   ;                                                                           
-- schemas/tables à analyser                                                   
   with CT1(SCH1, TAB1) as                                                     
       (select 'REC1', 'TR01' from SYSIBM.SYSDUMMY1   union all                
        select 'REC2', 'TA60' from SYSIBM.SYSDUMMY1                   
       )                                                                       
-- 1°) impact table space                                                      
select C1 as "Creator"                                                         
     , C2 as "Table"                                                           
     , C3 as "Type obj"                                                        
     , C4 as "Nom obj"                                                         
     , C5 as "Infos"                                                           
from (                                                                         
      select substr(CREATOR, 01, 08)   as C1                                   
           , substr(NAME, 01, 08)      as C2                                   
           , 'Table Space'             as C3                                   
           , cast(TSNAME as char(25))  as C4                                   
           , cast(DBNAME as char(10))  as C5                                                                                                         
           , 01                        as C6                                   
      from SYSTABLES                                                           
      inner join CT1                                                           
         on CREATOR = SCH1                                                     
        and NAME    = TAB1                                                     
      where TYPE = 'T'                                                         
-- 2°) impact vues                                                             
      union all                                                                
      select substr(BSCHEMA, 01, 08)   as C1                                   
           , substr(BNAME, 01, 08)     as C2                                   
           , 'View       '             as C3                                   
           , cast(DNAME as char(25))   as C4                                   
           , cast(DSCHEMA as char(10)) as C5                                   
           , 02                        as C6                                   
      from SYSDEPENDENCIES                                                     
      inner join CT1                                                           
         on BNAME        = TAB1                                                
        and BSCHEMA      = SCH1                                                
      where BTYPE = 'T'                                                        
-- 3°) impact index                                                            
      union all                                                                
      select substr(TBCREATOR, 01, 08)  as C1                                  
           , substr(TBNAME, 01, 08)     as C2                                  
           , 'Index      '              as C3                                  
           , cast(NAME as char(25))     as C4                                  
           , cast(case when uniquerule = 'P' and clustering='Y'                
                            then 'PK-Cluster'                                  
                       when uniquerule = 'P' and clustering='N'                
                            then 'PK-Non Cls'                                  
                       when uniquerule<> 'D' and clustering='Y'                
                            then 'U -Cluster'                                  
                       when uniquerule<> 'D' and clustering='N'                
                            then 'U -Non Cls'                                  
                       when uniquerule = 'D' and clustering='Y'                
                            then 'Dup-Clust.'                                  
                       when uniquerule = 'D' and clustering='N'                
                            then 'Dup-NonCls'                                  
                   end as char(10))     as C5                                  
           , 03                         as C6                                  
      from SYSINDEXES                                                          
      inner join CT1                                                           
         on TBNAME       = TAB1                                                
        and TBCREATOR    = SCH1                                                
-- 4°) impact alias                                                            
      union all                                                                
      select substr(SCH1, 01, 08)       as C1                                  
           , substr(TBNAME, 01, 08)     as C2                                  
           , 'Alias      '              as C3                                  
           , cast(NAME as char(25))     as C4                                  
           , cast(CREATOR as char(10))  as C5                                  
           , 04                         as C6                                  
      from SYSTABLES                                                           
      inner join CT1                                                           
         on TBNAME       = TAB1                                                
        and TBCREATOR    = SCH1                                                
      where TYPE = 'A'                                                         
-- 5°) impact rules "on delete"                                                
      union all                                                                
      select substr(REFTBCREATOR, 01, 08) as C1                                
           , substr(REFTBNAME, 01, 08)    as C2                                
           , 'Rule       '                as C3                                
           , cast(RELNAME as char(25))    as C4                                
           , cast(TBNAME as char(10))     as C5                                
           , 05                           as C6                                
      from SYSRELS                                                             
      inner join CT1                                                           
         on REFTBNAME    = TAB1                                                
        and REFTBCREATOR = SCH1                                                
-- 6°) impact triggers                                                         
      union all                                                                
      select substr(TBOWNER, 01, 08)       as C1                               
           , substr(TBNAME, 01, 08)        as C2                               
           , 'Trigger    '                 as C3                               
           , cast(substr(NAME, 01, 25) as char(25)) as C4                      
           , cast(case when TRIGTIME = 'B' then 'Before  '                     
                       when TRIGTIME = 'A' then 'After   '                     
                       else                     'Instead '                     
                  end                                                          
                  !! TRIGEVENT as char(10))         as C5                      
           , 06                            as C6                               
      from SYSTRIGGERS                                                         
      inner join CT1                                                           
         on TBNAME       = TAB1                                                
        and TBOWNER      = SCH1                                                
-- 7°) impact packages                                                         
      union all                                                                
      select substr(BQUALIFIER, 01, 08)    as C1                               
           , substr(BNAME, 01, 08)         as C2                               
           , 'Package    '                 as C3                               
           , cast(DNAME as char(25))       as C4                               
           , cast(DCOLLID as char(10))     as C5                               
           , 07                            as C6                               
      from SYSPACKDEP                                                          
      inner join CT1                                                           
         on BNAME        = TAB1                                                
        and BQUALIFIER   = SCH1                                                
     )                                                                         
order by C1, C2, C6                                                            
;

Exemple de résultat :