DatabaseProcApplicationCreatedLinks
sybsystemprocssp_downgrade_sysobjects  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** SP_DOWNGRADE_SYSOBJECTS
5     **
6     ** Description
7     **	sproc to reset the sysobjects schema back to it's original form.
8     **
9     ** Parameter:
10    **	@action	-	"display" to display all objects whose sysstat3
11    **			column has non-zero value.
12    **			"exec" to restore sysobjects back to original version.
13    **
14    ** Returns
15    **	0 - success
16    **	1 - error
17    {
18    */
19    create or replace procedure sp_downgrade_sysobjects @action sysname = "display"
20    as
21        begin -- {
22    
23            declare @dbname sysname,
24                @orig_dbname sysname,
25                @objname longsysname,
26                @objstat3 unsigned smallint,
27                @retval int,
28                @dbid int,
29                @sqlcmd varchar(1024),
30                @baddbstat int,
31                @baddbstat2 int,
32                @baddbstat3 int,
33                @warnings int,
34                @tab_str char(3),
35                @blank_str char(3)
36    
37            select @tab_str = '%	%'
38            select @blank_str = '% %'
39    
40            if (@action not in ("display", "exec"))
41            begin
42                print "Usage: sp_downgrade_sysobjects [display | exec]"
43                return 1
44            end
45    
46            print "Checking sysobjects in preparation for downgrade"
47    
48            /*
49            ** COPY from sp_downgrade.
50            ** Downgrade only those databases that are writable 
51            **
52            ** sysdatabases.status not in 0x20 (in load), 0x40 (not rec), 
53            ** 0x80 (bypass), 0x100 (suspect), 0x400 (rdonly), 0x8000 (emergency)
54            **
55            ** sysdatabases.status2 not in 0x10 (offline), 0x400 (online for 
56            ** standby access)
57            **
58            ** sysdatabases.status3 not in 0x1 (user proxy), 0x2 (ha proxy),
59            ** 0x8 (shutdown), 0x10 (failedover), 0x80 (quiesced), 0x400000 
60            ** (archive)
61            */
62            select @baddbstat = 34272, @baddbstat2 = 1040,
63                @baddbstat3 = 4194459
64    
65            select dbid
66            into #sysdatabases1
67            from master.dbo.sysdatabases
68            where status & @baddbstat = 0
69                and status2 & @baddbstat2 = 0
70                and status3 & @baddbstat3 = 0
71            order by dbid
72    
73            /*
74            ** Temp table is created to hold object name whose sysstat3
75            ** is not zero, as cursor cannot be created based on a select
76            ** statement containing variable "@dbname" and composing cursor
77            ** declaration and "@dbname" into immediate statement doesn't
78            ** work either.
79            */
80            create table #stat3obj
81            (
82                name longsysname,
83                sysstat3 unsigned smallint
84            )
85    
86            set @warnings = 0
87    
88            /* A cursor to traverse sysdatabases */
89            declare sysdbc2 cursor for
90            select dbid from #sysdatabases1
91    
92            open sysdbc2
93            fetch sysdbc2 into @dbid
94    
95            while (@@sqlstatus = 0)
96            begin -- {
97    
98                select @dbname = db_name(@dbid)
99                select @orig_dbname = @dbname
100               /*
101               ** Database name in which there is tab or blank must
102               ** be delimited with '[]' in sql cmd.
103               */
104               if ((patindex(@tab_str, @dbname) > 0)
105                       or (patindex(@blank_str, @dbname) > 0))
106               begin
107                   select @dbname = '[' + @orig_dbname + ']'
108               end
109   
110               set @retval = 0
111               select @sqlcmd = 'select @retval = count(1) from ' + @dbname +
112                   '..sysobjects where sysstat3 != 0 ' +
113                   'and type = ''U'' '
114               exec (@sqlcmd)
115   
116               /*
117               ** If rows having non-zero sysstat3 are found, print
118               ** them out as warnings in "display" mode or return as
119               ** errors in "exec" mode.
120               */
121               if @retval != 0
122               begin
123                   /* Report warnings of these tables */
124                   select @sqlcmd = 'insert #stat3obj ' +
125                       'select name, sysstat3 from ' + @dbname
126                       + '..sysobjects where sysstat3 != 0 ' +
127                       'and type = ''U'' '
128                   exec (@sqlcmd)
129   
130                   declare sysdbc3 cursor for
131                   select name, sysstat3 from #stat3obj
132   
133                   open sysdbc3
134                   fetch sysdbc3 into @objname, @objstat3
135                   while (@@sqlstatus = 0)
136                   begin -- {
137                       /*
138                       ** Check status3 of sysobjects row,
139                       ** 0x0001 - FULL logging mode.
140                       ** 0x0002 - MINIMAL logging mode.
141                       ** 0x8000 - Incremental transfer eligible.
142                       */
143                       if (@objstat3 & 1 = 1)
144                       begin
145                           print "Error: Table '%1!..%2!' with FULL logging mode should be altered back to default logging mode before downgrade.", @orig_dbname, @objname
146                           select @warnings = @warnings + 1
147                       end
148                       if (@objstat3 & 2 = 2)
149                       begin
150                           print "Error: Table '%1!..%2!' with MINIMAL logging mode should be altered back to default logging mode before downgrade.", @orig_dbname, @objname
151                           select @warnings = @warnings + 1
152                       end
153                       if (@objstat3 & 32768 = 32768)
154                       begin
155                           print "Error: Table '%1!..%2!' eligible for incremental transfer should be altered to turn off incremental transfer before downgrade.", @orig_dbname, @objname
156                           select @warnings = @warnings + 1
157                       end
158                       fetch sysdbc3 into @objname, @objstat3
159                   end --}
160                   close sysdbc3
161                   deallocate cursor sysdbc3
162                   delete from #stat3obj
163               end
164   
165               /*
166               ** User must have altered the tables having non-zero
167               ** sysstat3 column. Remove sysstat3 for sysobjects
168               ** and restore objspare in "exec" mode. 
169               */
170               if ((@action = "exec") and (@warnings = 0))
171               begin
172                   select @sqlcmd = 'delete ' + @dbname +
173                       '..syscolumns where id = 1 ' +
174                       'and name = ''sysstat3'' ' +
175                       'update ' + @dbname +
176                       '..syscolumns set type = 56,' +
177                       'length = 4, usertype = 7 ' +
178                       'where id = 1 and name = ''objspare'' '
179                   exec (@sqlcmd)
180               end
181   
182               fetch sysdbc2 into @dbid
183           end --}
184   
185           close sysdbc2
186           deallocate cursor sysdbc2
187   
188           if (@warnings > 0)
189           begin
190               print "There are %1! errors during checking sysobjects.", @warnings
191               print "Please run alter table to set these reported tables to normal mode before downgrade."
192               return 1
193           end
194   
195           return 0
196       end -- }
197   
198   


exec sp_procxmode 'sp_downgrade_sysobjects', 'AnyMode'
go

Grant Execute on sp_downgrade_sysobjects to public
go
DEFECTS
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysdbc3 131
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 19
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_downgrade_sysobjects  
 MNER 3 No Error Check should check @@error after select into 65
 MNER 3 No Error Check should check @@error after delete 162
 MUCO 3 Useless Code Useless Begin-End Pair 21
 MUCO 3 Useless Code Useless Brackets 40
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 188
 MUIN 3 Column created using implicit nullability 80
 CUPD 2 Updatable Cursor Marker (updatable by default) 90
 CUPD 2 Updatable Cursor Marker (updatable by default) 131
 MDYS 2 Dynamic SQL Marker 114
 MDYS 2 Dynamic SQL Marker 128
 MDYS 2 Dynamic SQL Marker 179
 MTR1 2 Metrics: Comments Ratio Comments: 32% 19
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 15dec - 3exi + 2 19
 MTR3 2 Metrics: Query Complexity Complexity: 75 19

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#sysdatabases1 (1) 
reads table master..sysdatabases (1)  
read_writes table tempdb..#stat3obj (1) 

CALLERS
called by proc sybsystemprocs..sp_downgrade