DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpdefrag  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Fri Sep 21 06:46:22 2012 
4     */
5     /*
6     ** raiserror Messages for helpdefrag [Total 4]
7     **
8     ** 17460, "Object must be in the current database."
9     ** 17461, "Object does not exist in this database."
10    ** 19305, "There is no partition named '%1!' for table '%2!', index '%3!'."
11    ** 19989, "Only user tables with data-only locking scheme can be defragmented using reorg defrag."
12    */
13    /*
14    ** sp_getmessage Messages for helpdefrag [Total 0]
15    */
16    /*
17    ** End spgenmsgs.pl output.
18    */
19    
20    /*
21    ** SP_HELPDEFRAG
22    **
23    ** Report information about the state of defragmentation on a certain
24    ** table or data partition resulting from running 'reorg defrag' on the
25    ** object.
26    ** 
27    ** Parameters:
28    **	@tablename	- Table whose defragmentation info is sought.
29    **
30    **			  If null, defragmentation status of all the tables
31    **			  in this database eligible for defragmentation
32    **			  would be reported.
33    **			
34    ** 	@partitionname 	- Data partition whose defragmentation info is sought.
35    **
36    **			  If null, defragmentation status of all the data
37    **			  partitions in the given table would be reported.
38    **
39    */
40    create or replace procedure sp_helpdefrag
41        @tablename varchar(317) = NULL, /* the table whose defrag status is
42        ** sought
43        */
44        @partitionname varchar(255) = NULL /* the data partition whose defrag
45    					** status is sought
46    					*/
47    as
48        begin -- {
49            declare @type char(2) /* the object type */
50                , @type2 int /* locking scheme */
51                , @dbname varchar(255)
52                , @tabname varchar(255)
53                , @dbid int
54                , @tabid int
55                , @ptnid int
56                , @procname varchar(20)
57                , @msg_17110 varchar(1024)
58    
59            if @@trancount = 0
60            begin
61                set chained off
62            end
63    
64            set transaction isolation level 1
65    
66            select @dbid = db_id()
67    
68            /*
69            **  Check to see that the tablename is local.
70            */
71            if @tablename is not null
72            begin
73                /*
74                ** Get the dbname and ensure that the object is in the
75                ** current database. Also get the table name - this is later
76                ** needed to see if information is being requested for syslogs.
77                */
78                execute sp_namecrack @tablename,
79                    @db = @dbname output,
80                    @object = @tabname output
81                if @dbname is not NULL
82                begin
83                    /*
84                    ** 17460, "Object must be in the current database." 
85                    */
86                    if (@dbname != db_name())
87                    begin
88                        raiserror 17460
89                        return (1)
90                    end
91                end
92    
93    
94                /*
95                **  Does the object exist?
96                */
97                select @tabid = object_id(@tabname)
98    
99                if not exists (select 1
100                       from sysobjects
101                       where id = @tabid)
102               begin
103                   /*
104                   ** 17461, "Object does not exist in this database."
105                   */
106                   raiserror 17461
107                   return (1)
108               end
109   
110               /*
111               **  See if it's an object on which defrag is allowed.
112               **  It must be a user table with data-only locking scheme.
113               **  for which sysstat2 must have 32768(datarow locking) bit 
114               **  or 16384(datapage locking) bit on. 
115               **  (32768 | 16384 = 49152)
116               */
117               select @type = type,
118                   @type2 = sysstat2 & 49152
119               from sysobjects
120               where id = @tabid
121               if ((@type != 'U') or (@type2 = 0))
122               begin
123                   /*
124                   ** 19989, "'%1!' is only applicable to user tables with %2! scheme."
125                   */
126                   select @procname = "sp_helpdefrag"
127                   exec sp_getmessage 17110, @msg_17110 output
128                   raiserror 19989, @procname, @msg_17110
129                   return (1)
130               end
131           end
132   
133           /*
134           **  Check to see that the partitionname provided is indeed a data 
135           **  partition belonging to this table.
136           */
137           if @partitionname is not null
138           begin
139               select @ptnid = partitionid from syspartitions
140               where id = @tabid and indid = 0
141                   and name = @partitionname
142               /*
143               **  This data partition doesn't exist in this table so return.
144               */
145               if @ptnid is null
146               begin
147                   /*
148                   ** 19305, "There is no partition named '%1!' for table 
149                   ** '%2!', index '%3!'."
150                   */
151                   raiserror 19305, @partitionname, @tabname, @tabname
152                   return (1)
153               end
154           end
155   
156           set nocount on
157   
158           /*
159           ** If input tablename is null, then we want the status of 
160           ** defragmentation of each of the tables in this database eligible 
161           ** for 'reorg defrag'.
162           */
163           if @tablename is null
164           begin
165               select o.id
166                   , name = o.name
167                   , defrag = defrag_status(@dbid, o.id)
168                   , frag_index = 0.00
169                   , executing = convert(tinyint, 0)
170                   , last_run = convert(datetime NULL, NULL)
171                   , pct_defrag = convert(tinyint, 0)
172               into #defrag_info1
173               from sysobjects o
174               where ((o.sysstat & 15) = 3)
175                   and ((o.sysstat2 & 49152) != 0)
176   
177               /*
178               ** update #defrag_info to pull out individual column 
179               ** values parsing the defrag_status() built-in output
180               ** stored in defrag column.
181               */
182               update #defrag_info1
183               set frag_index = dbo.sp_f_getfloat(di.defrag, 'frag index',
184                       default, default)
185                   , executing = dbo.sp_f_getuint(di.defrag, 'executing',
186                       default, default)
187                   , last_run = dbo.sp_f_getdate(di.defrag, 'last run',
188                       default, default)
189                   , pct_defrag = dbo.sp_f_getuint(di.defrag, 'pct defrag',
190                       default, default)
191               from #defrag_info1 di
192   
193               exec sp_autoformat @fulltabname = #defrag_info1
194                   , @selectlist = "[table]=name, frag_index, pct_defrag, executing, last_run"
195                   , @orderby = "order by executing desc, pct_defrag asc"
196               drop table #defrag_info1
197           end
198   
199           else
200           begin
201               /* Table name and/or partition name is specified. */
202               select p.id
203                   , name = @tablename
204                   , ptnname = convert(varchar(255) NULL, p.name)
205                   , defrag = defrag_status(@dbid, p.id, p.partitionid)
206                   , frag_index = 0.00
207                   , executing = convert(tinyint, 0)
208                   , last_run = convert(datetime NULL, NULL)
209                   , pct_defrag = convert(tinyint, 0)
210                   , table_info = convert(tinyint, 0)
211               into #defrag_info2
212               from syspartitions p
213               where ((p.id = @tabid) AND (p.indid = 0))
214                   and ((@ptnid IS NULL) OR (p.partitionid = @ptnid))
215   
216               /*
217               ** The temp table is populated with each row corresponding to
218               ** a data partition. If no partition name is input to the
219               ** proc and partition count is greater than 1, insert a row 
220               ** corresponding to the table too which gives defrag info for
221               ** the whole table (distinguish this row by flagging 
222               ** table_info as 1).
223               */
224               if ((@partitionname is null))
225               begin
226                   insert into #defrag_info2
227                   values (0, @tablename, NULL, defrag_status(@dbid, @tabid),
228                       0.0, 0, NULL, 0, 1)
229               end
230   
231               /*
232               ** update #defrag_info to pull out individual column 
233               ** values parsing the defrag_status() built-in output
234               ** stored in defrag column.
235               */
236               update #defrag_info2
237               set frag_index = dbo.sp_f_getfloat(di.defrag, 'frag index',
238                       default, default)
239                   , executing = dbo.sp_f_getuint(di.defrag, 'executing',
240                       default, default)
241                   , last_run = dbo.sp_f_getdate(di.defrag, 'last run',
242                       default, default)
243                   , pct_defrag = dbo.sp_f_getuint(di.defrag, 'pct defrag',
244                       default, default)
245               from #defrag_info2 di
246   
247               exec sp_autoformat @fulltabname = #defrag_info2
248                   , @selectlist = "[table]=name, [partition]=ptnname, frag_index, pct_defrag, executing, last_run"
249                   , @orderby = "order by table_info desc, pct_defrag asc"
250               drop table #defrag_info2
251           end
252       end -- }
253   


exec sp_procxmode 'sp_helpdefrag', 'AnyMode'
go

Grant Execute on sp_helpdefrag to public
go
DEFECTS
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 193
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 247
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 140
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 213
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 101
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 120
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 140
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 165
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 167
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 202
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 205
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 213
 MGTP 3 Grant to public sybsystemprocs..sp_helpdefrag  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..syspartitions  
 MNER 3 No Error Check should check return value of exec 78
 MNER 3 No Error Check should check return value of exec 127
 MNER 3 No Error Check should check @@error after select into 165
 MNER 3 No Error Check should check @@error after update 182
 MNER 3 No Error Check should check return value of exec 193
 MNER 3 No Error Check should check @@error after select into 202
 MNER 3 No Error Check should check @@error after insert 226
 MNER 3 No Error Check should check @@error after update 236
 MNER 3 No Error Check should check return value of exec 247
 MUCO 3 Useless Code Useless Begin-End Pair 48
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 224
 MUUF 3 Update or Delete with Useless From Clause 182
 MUUF 3 Update or Delete with Useless From Clause 236
 QCTC 3 Conditional Table Creation 165
 QCTC 3 Conditional Table Creation 202
 QISO 3 Set isolation level 64
 QPNC 3 No column in condition 214
 MSUB 2 Subquery Marker 99
 MTR1 2 Metrics: Comments Ratio Comments: 42% 40
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 18dec - 5exi + 2 40
 MTR3 2 Metrics: Query Complexity Complexity: 73 40

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..syspartitions  
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
calls proc sybsystemprocs..sp_namecrack  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
writes table tempdb..#defrag_info1 (1) 
writes table tempdb..#defrag_info2 (1)