DatabaseProcApplicationCreatedLinks
sybsystemprocssp_downgrade_sysdams  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** SP_DOWNGRADE_SYSDAMS
4     **
5     ** Description
6     **	Drop SYSDAMS in all databases
7     **
8     ** Parameters
9     **	@dbname - Database or NULL for all. Default is NULL.
10    **	@cmd - 'prepare' or 'downgrade'. Default is 'prepare'.
11    **	@show_cmd - 1 - verbose, 0 - quiet. Default is 0.
12    **
13    ** Returns
14    **	0 - success
15    **	1 - error
16    {
17    */
18    create or replace procedure sp_downgrade_sysdams @dbname sysname = null,
19        @cmd varchar(10) = 'prepare', @show_cmd int = 0
20    as
21        begin
22            declare @dbid int,
23                @retval int,
24                @damstat int
25    
26            set proc_return_status off
27            set nocount on
28    
29            if not @cmd in ('prepare', 'downgrade')
30                return 1
31    
32            if @cmd = 'downgrade'
33                goto downgrade_sysdams
34    
35            print "Checking sysdams in preparation for downgrade"
36    
37            select @damstat = number from master..spt_values
38            where name = "allow incremental dumps"
39    
40            select @retval = count(1) from master..sysdatabases
41            where (status4 & @damstat) != 0
42                and (@dbname is null or name = @dbname)
43    
44            if @retval != 0
45            begin
46                declare sysdbc0 cursor for
47                select name from master..sysdatabases
48                where (status4 & @damstat) != 0
49                    and (@dbname is null or name = @dbname)
50    
51                open sysdbc0
52                fetch sysdbc0 into @dbname
53    
54                while (@@sqlstatus = 0)
55                begin
56                    print "Error: Database '%1!' 'allow incremental dumps' option should be set to FALSE before downgrade.", @dbname
57                    fetch sysdbc0 into @dbname
58                end
59    
60                close sysdbc0
61                deallocate cursor sysdbc0
62    
63            end
64    
65            return @retval
66    
67    downgrade_sysdams:
68    
69            select @retval = 0
70            dbcc traceon(3717)
71    
72            declare c cursor for
73            select dbid from master.dbo.sysdatabases
74            where (@dbname is null or name = @dbname)
75    
76            open c
77            fetch c into @dbid
78            while (@@sqlstatus = 0)
79            begin
80                if (@show_cmd = 1)
81                    print "dbcc dam(%1!, 'drop')", @dbid
82    
83                dbcc dam(@dbid, 'drop')
84    
85                if (@@error != 0)
86                    select @retval = 1
87                fetch c into @dbid
88            end
89    
90            close c
91            deallocate cursor c
92            dbcc traceoff(3717)
93    
94            return @retval
95        end
96    


exec sp_procxmode 'sp_downgrade_sysdams', 'AnyMode'
go

Grant Execute on sp_downgrade_sysdams to public
go
DEFECTS
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc0 47
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause c 73
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_downgrade_sysdams  
 MUCO 3 Useless Code Useless Begin-End Pair 21
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 85
 QAFM 3 Var Assignment from potentially many rows 37
 QPNC 3 No column in condition 42
 QPNC 3 No column in condition 49
 QPNC 3 No column in condition 74
 CUPD 2 Updatable Cursor Marker (updatable by default) 47
 CUPD 2 Updatable Cursor Marker (updatable by default) 73
 MTR1 2 Metrics: Comments Ratio Comments: 15% 18
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 13dec - 1exi + 2 18
 MTR3 2 Metrics: Query Complexity Complexity: 58 18

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysdatabases (1)  
reads table master..spt_values (1)  

CALLERS
called by proc sybsystemprocs..sp_downgrade  
called by proc sybsystemprocs..sp_downgrade_esd