DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_object_init  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     **	SP_SPACEUSAGE_OBJECT_INIT
4     **
5     **	The sub-procedure that initializes the temporary table #spaceusageinfo
6     **	with space usage information depending on the action. For "display" or
7     **	"display summary" or "archive" action, only the raw data extracted from
8     **	different system tables is loaded into #spaceusageinfo (all other data
9     **	will have to be computed in the next step to make the information 
10    **	complete) whereas for the action "report" and "report summary", the 
11    **	processed data in the archive table is loaded into the #spaceusageinfo 
12    **	table. Called by sp_spaceusage_object.
13    **
14    **	Parameters
15    **		@actionword	- The action to be performed.
16    **		@nouname	- Was no owner name provided by the user?
17    **		@uname		- Owner/user name.
18    **		@tname		- Table name.
19    **		@iname		- Index name.
20    **		@entity_type	- The type of entity.
21    **		@archivetabname	- The 3-part archive table name.
22    **		@from_date	- Date from when on to get archived data.
23    **		@to_date	- Date till which to get archvied data.
24    **
25    **	Returns
26    **		0 - if all goes well
27    **		6 - archive table not found
28    **	    other - error while execution 
29    {
30    */
31    create or replace procedure sp_spaceusage_object_init
32    (
33        @actionword varchar(20)
34        , @nouname bit
35        , @uname varchar(255)
36        , @tname varchar(255)
37        , @iname varchar(255)
38        , @entity_type varchar(12)
39        , @archivetabname varchar(320) = NULL
40        , @from_date varchar(30) = NULL
41        , @to_date varchar(30) = NULL
42    )
43    as
44        begin -- {
45    
46            declare @entity_table varchar(6)
47                , @entity_index varchar(6)
48    
49                , @action_display varchar(8)
50                , @action_display_summary varchar(16)
51                , @action_report varchar(7)
52                , @action_report_summary varchar(15)
53                , @action_archive varchar(8)
54    
55                , @esdnumstr varchar(20)
56                , @ebfnumstr varchar(20)
57                , @esdnum int
58                , @ebfnum int
59    
60                , @returnStatus int
61                , @sqlstmt varchar(4096)
62                , @whoami varchar(40)
63                , @msg varchar(256)
64    
65            select @whoami = "sp_spaceusage_object_init"
66    
67                , @entity_table = "table"
68                , @entity_index = "index"
69    
70                , @action_display = "display"
71                , @action_display_summary = "display summary"
72                , @action_report = "report"
73                , @action_report_summary = "report summary"
74                , @action_archive = "archive"
75    
76            /* Extract the ESD number and the EBF number from the version string.*/
77            exec sp_versioncrack @@version, "ESD", @esdnumstr out, @esdnum out
78            exec sp_versioncrack @@version, "EBF", @ebfnumstr out, @ebfnum out
79    
80            /*
81            ** NOTE1: When user does not supply owner name of a table, then current
82            ** user is implicitly assumed. As is the convention, in case the owner
83            ** name is omitted and there is no table owned by current user that
84            ** qualifies, but one owned by DBO qualifies, we will include that in
85            ** the result. 
86            ** 
87            ** For example, asumme there are tables t1 and t2 owned by user1, tables
88            ** t1 and t3 owned by dbo and table t1 owned user2 in a database. When 
89            ** user1 supplies user1.t1, only user1's table t1 should be included.
90            ** When user1 supplies user2.t1, only user2's table t1 should be
91            ** included. When user1 omits the owner and supplies t1, again only
92            ** user1's table t1 should be included as it exists. But if user1 omits
93            ** the owner and supplies t3, dbo's table t3 should be included as no
94            ** table t3 owned by user1 exists while t3 owned by dbo does.
95            **
96            ** Due to this reason the WHERE clause handling the owner name has a
97            ** sub-query embedded.
98            **
99            ** NOTE2: In case of 'display', 'display summary' and 'archive' action
100           ** for the entity type index, we need to include the data layer
101           ** information for all the table whose index qualifies. This is required
102           ** as we need the estimates for data layer in order to make estimates
103           ** about the index layer. Hence, an additional condition is added to the
104           ** WHERE clause handling the index name to take care of this.
105           */
106   
107           if @actionword in (@action_display
108                   , @action_display_summary
109                   , @action_archive)
110   
111           begin -- { 	-- Display/Archive mode
112   
113               /* 
114               ** Collect all the necessary raw data or meta data for 
115               ** estimation. 
116               **
117               */
118               insert into #spaceusageinfo
119               select
120                   @@version_number as VersionNum
121                   , @esdnum as ESDNum
122                   , @ebfnum as EBFNum
123                   , getdate() as ArchiveDateTime
124                   , @@servername as ServerName
125                   , @@maxpagesize as MaxPageSize
126                   , db_name() as DBName
127                   , user_name(o.uid) as OwnerName
128                   , o.name as TableName
129                   , i.id as Id
130                   , p.indid as IndId
131                   , i.name as IndexName
132                   , p.partitionid as PtnId
133                   , p.name as PtnName
134                   , p.data_partitionid as DataPtnId
135   
136                   -- These are the values which needs to be accurate in
137                   -- order for us to correctly report the estimated data
138                   -- or reserved page counts. A recent 'update table
139                   -- statistics'is necessary for these values to be
140                   -- accurate.
141                   --
142                   , RowSize = case
143                       when p.indid > 1
144                       then convert(decimal(10, 4),
145                       t.leafrowsize)
146                       else convert(decimal(10, 4),
147                           t.datarowsize)
148                   end
149   
150                   -- Compute the row count using the built-in. As the
151                   -- number of rows is not applicable for the index layer,
152                   -- it is set to NULL for indid > 0. For indid = 1, the
153                   -- dummy data layer row that we add in the next step
154                   -- would have the value set appropriately.
155                   --
156                   , NumRows = case
157                       when p.indid > 0
158                       then NULL
159                       else row_count(db_id(), i.id
160                               , p.partitionid)
161                   end
162                   , t.rowcnt as RowCount_ts
163   
164                   , t.forwrowcnt as NumFwdRows
165                   , t.delrowcnt as NumDelRows
166                   , t.emptypgcnt as EmptyPageCount
167   
168                   -- The following built-ins return 0 in case of invalid
169                   -- inputs. So, no need to check for NULL. Except for
170                   -- used_pages(), the built-ins return only the index
171                   -- layer pages for indid=1. For used_pages(), however,
172                   -- they return both data and index layer used pages. The
173                   -- data layer used pages can be got by passing indid=0
174                   -- to the built-in and then subtracted to get the index
175                   -- layer used pages.
176                   --
177                   , data_pages(db_id(), i.id, p.indid, p.partitionid)
178                   as DataPageCount
179                   , used_pages(db_id(), i.id, p.indid, p.partitionid)
180                   - case p.indid
181                       when 1 then used_pages(db_id(), i.id, 0,
182                           p.partitionid)
183                       else 0
184                   end
185                   as UsedPageCount
186                   , reserved_pages(db_id(), i.id, p.indid, p.partitionid)
187                   as RsvdPageCount
188   
189                   -- Placeholder for space utilizaton and cluster ratio
190                   -- and other statistics collected using the
191                   -- derived_stat() built-in.
192                   --
193                   , convert(decimal(9, 2), 0) as SpUtil
194                   , convert(decimal(5, 4), 0) as DPCR
195                   , convert(decimal(5, 4), 0) as DRCR
196                   , convert(decimal(5, 4), 0) as IPCR
197                   , convert(decimal(5, 4), 0) as LGIO
198   
199                   -- Extract the space management parameters.
200                   , fill_factor as FF
201                   , maxrowsperpage as MRPP
202                   , exp_rowsize as ERS
203                   , res_page_gap as RPG
204   
205                   -- Other columns from systabstats that may be
206                   -- interesting.
207                   --
208                   , t.leafcnt as LeafPageCount
209                   , t.indexheight as IndexHeight
210                   , t.oamapgcnt as OAMAPageCount
211                   , t.extent0pgcnt as Extent0PageCount
212                   , t.status as Status
213                   , o.sysstat as Sysstat
214                   , o.sysstat2 as Sysstat2
215   
216                   -- Some input and output columns that would be computed.
217                   , convert(decimal(10, 4), 0) as NonLeafRowSize
218                   , NULL as ExpIndexHeight
219                   , 0 as ExpDataPageCount
220                   , 0 as ExpUsedPageCount
221                   , 0 as ExpRsvdPageCount
222                   , 0 as ExpLeafPageCount
223                   , convert(decimal(7, 2), 0) as PctBloatUsedPages
224                   , convert(decimal(7, 2), 0) as PctBloatRsvdPages
225                   , convert(decimal(7, 2), 0) as PctBloatLeafPages
226                   , convert(decimal(5, 2), 0) as ExtentUtil
227                   , convert(decimal(5, 2), 0) as PctEmptyPages
228                   , convert(decimal(5, 2), 0) as PctFwdRows
229   
230                   -- Auxilliary columns to facilitate estimate computation
231                   , convert(tinyint, ((o.sysstat2 & 57344) / 16384)
232                   ) as LockScheme
233                   , convert(smallint, - 1) as NumVarCols
234                   , convert(bit, 0) as HasAPLCI
235                   , convert(date, t.statmoddate) -- StatModDate
236   
237                   -- List of generated columns that will not be archived.
238                   --
239                   , convert(float, 0.0) as SpacePerPage
240                   , convert(float, 0.0) as CalcRowsPerPage
241                   , convert(float, 0.0) as EmptyPages
242                   , convert(float, 0.0) as DataPages
243                   , convert(float, 0.0) as UsedPages
244                   , convert(float, 0.0) as RsvdPages
245                   , convert(float, 0.0) as LeafPages
246                   , convert(float, 0.0) as ExpDataPages
247                   , convert(float, 0.0) as ExpUsedPages
248                   , convert(float, 0.0) as ExpRsvdPages
249                   , convert(float, 0.0) as ExpLeafPages
250   
251               from sysindexes i
252               , syspartitions p
253               , systabstats t
254               , sysobjects o
255   
256               where o.name like @tname
257   
258                   -- Refer to NOTE1 at line# 140.
259                   and (user_name(o.uid) like @uname
260                       or (@nouname = 1
261                           and not exists (select 1
262                               from sysobjects o2
263                               where o2.name = o.name
264                                   and user_name(o2.uid) = @uname)
265                           and user_name(o.uid) = "dbo"
266                       )
267                   )
268   
269                   -- Only look for user/system tables.
270                   and type in ('U', 'S')
271   
272                   -- Skip fake tables.
273                   and ((o.sysstat & 1024) != 1024)
274   
275                   -- Skip proxy tables.
276                   and ((o.sysstat2 & 1024) != 1024)
277   
278                   -- Skip tables with no real storage.
279                   and (p.indoampage != 0
280                       or p.datoampage != 0)
281   
282                   and o.id = i.id
283                   and p.id = i.id
284                   and p.indid = i.indid
285   
286                   -- Do a right join with systabstats so as to include a row
287                   -- corresponding to indid = 255, although there is no row for
288                   -- it present in systabstats.
289                   -- 
290                   and t.id =* p.id
291                   and t.indid =* p.indid
292                   and t.partitionid =* p.partitionid
293   
294                   -- Refer to NOTE2 at line# 158.
295                   and (i.name like @iname
296                       or (i.indid in (0, 1)
297                           and o.id in (select i2.id
298                               from sysindexes i2
299                               where name like @iname)
300                       )
301                   )
302   
303               if (@@error != 0)
304                   return (@@error)
305   
306               /*
307               ** Add a dummy row with indid = 0, for each indid = 1 column.
308               ** This is to separate the index and data layer for indid = 1
309               ** case, which is otherwise difficult.
310               */
311               insert into #spaceusageinfo
312               select VersionNum
313                   , ESDNum
314                   , EBFNum
315                   , ArchiveDateTime
316                   , ServerName
317                   , MaxPageSize
318                   , DBName
319                   , OwnerName
320                   , TableName
321                   , Id
322                   , IndId = 0
323                   , IndexName = TableName
324                   , PtnId
325                   , PtnName
326                   , DataPtnId
327                   , RowSize
328                   , NumRows = row_count(db_id(), Id, PtnId)
329                   , RowCount_ts
330                   , NumFwdRows
331                   , NumDelRows
332                   , EmptyPageCount
333                   , DataPageCount = data_pages(db_id(), Id, 0, PtnId)
334                   , UsedPageCount = used_pages(db_id(), Id, 0, PtnId)
335                   , RsvdPageCount = reserved_pages(db_id(), Id, 0, PtnId)
336                   , SpUtil
337                   , DPCR
338                   , DRCR
339                   , IPCR
340                   , LGIO
341                   , FF
342                   , MRPP
343                   , ERS
344                   , RPG
345                   , LeafPageCount
346                   , IndexHeight
347                   , OAMAPageCount
348                   , Extent0PageCount
349                   , Status
350                   , Sysstat
351                   , Sysstat2
352                   , NonLeafRowSize
353                   , ExpIndexHeight
354                   , ExpDataPageCount
355                   , ExpUsedPageCount
356                   , ExpRsvdPageCount
357                   , ExpLeafPageCount
358                   , PctBloatUsedPages
359                   , PctBloatRsvdPages
360                   , PctBloatLeafPages
361                   , ExtentUtil
362                   , PctEmptyPages
363                   , PctFwdRows
364                   , LockScheme
365                   , NumVarCols
366                   , HasAPLCI = 1
367                   , StatModDate
368                   , SpacePerPage
369                   , CalcRowsPerPage
370                   , EmptyPages
371                   , DataPages
372                   , UsedPages
373                   , RsvdPages
374                   , LeafPages
375                   , ExpDataPages
376                   , ExpUsedPages
377                   , ExpRsvdPages
378                   , ExpLeafPages
379   
380               from #spaceusageinfo
381               where IndId = 1
382   
383               if (@@error != 0)
384                   return (@@error)
385   
386           end -- }	-- Display/Archive mode
387   
388           else if @actionword in (@action_report
389                   , @action_report_summary)
390   
391           begin -- {	-- Report action
392   
393               -- Check for valid archive table name is already done by the
394               -- calling procedure sp_spaceusage_object.
395   
396               select @sqlstmt = " INSERT INTO #spaceusageinfo "
397                   + " SELECT 	 * "
398                   + ", SpacePerPage" + "=0"
399                   + ", CalcRowsPerPage" + "=0"
400                   + ", EmptyPages" + "=0"
401                   + ", DataPages" + "=0"
402                   + ", UsedPages" + "=0"
403                   + ", RsvdPages" + "=0"
404                   + ", LeafPages" + "=0"
405                   + ", ExpDataPages" + "=0"
406                   + ", ExpUsedPages" + "=0"
407                   + ", ExpRsvdPages" + "=0"
408                   + ", ExpLeafPages" + "=0"
409                   + " FROM " + @archivetabname + " o "
410                   + " WHERE DBName LIKE '" + db_name() + "'"
411                   + "   AND TableName LIKE '" + @tname + "'"
412                   + "   AND IndexName LIKE '" + @iname + "'"
413   
414               -- Refer to NOTE1 at line#140
415               select @sqlstmt = @sqlstmt
416                   + " AND "
417                   + "(OwnerName LIKE '" + @uname + "'"
418                   + " OR ( 1=" + convert(char(1), @nouname)
419                   + " AND NOT EXISTS("
420                   + " SELECT 1"
421                   + " FROM "
422                   + @archivetabname
423                   + " i1"
424                   + " WHERE "
425                   + "i1.TableName"
426                   + "=o.TableName"
427                   + " AND "
428                   + "i1.OwnerName"
429                   + "='" + @uname
430                   + "')"
431                   + " AND o.OwnerName = 'dbo'"
432                   + ")"
433                   + ")"
434   
435               -- The ArchiveDateTime part of the query does the following.
436               -- When both @from_date and @to_date are NULL, it looks for the
437               -- latest ArchiveDateTime for each object that qualifies and
438               -- selects that entry into the #spaceusageinfo table. If only
439               -- @from_date is NULL, it selects all data till @to_date. And
440               -- when only @to_date is missing, it assumes @to_date to be
441               -- today.
442               --
443               if @from_date is NULL
444               begin
445                   if @to_date is NULL
446                   begin -- both dates are null
447                       select @sqlstmt = @sqlstmt
448                           + " AND ArchiveDateTime "
449                           + " = (SELECT "
450                           + "max(ArchiveDateTime)"
451                           + " FROM " + @archivetabname
452                           + " i2 "
453                           + " WHERE i2.DBName=o.DBName"
454                           + " AND i2.OwnerName"
455                           + "=o.OwnerName"
456                           + " AND i2.TableName"
457                           + "=o.TableName"
458                           + " AND i2.IndexName"
459                           + "=o.IndexName"
460                           + " AND (i2.ServerName is NULL"
461                           + " or i2.ServerName"
462                           + "=o.ServerName)"
463                           + ")"
464                   end
465                   else
466                   begin -- only from_date is null
467                       select @sqlstmt = @sqlstmt
468                           + " AND ArchiveDateTime "
469                           + " <= '" + @to_date + "'"
470                   end
471               end
472               else
473               begin
474                   if @to_date is NULL
475                   begin -- only to_date is null
476                       select @sqlstmt = @sqlstmt
477                           + " AND ArchiveDateTime "
478                           + " >= '" + @from_date + "'"
479                           + " AND ArchiveDateTime "
480                           + " <= getdate()"
481                   end
482                   else
483                   begin -- both dates are non-null
484                       select @sqlstmt = @sqlstmt
485                           + " AND ArchiveDateTime "
486                           + " >= '" + @from_date + "'"
487                           + " AND ArchiveDateTime "
488                           + " <= '" + @to_date + "'"
489                   end
490               end
491   
492               exec @returnStatus = sp_exec_SQL @sqlstmt, @whoami
493   
494               if @returnStatus != 0
495                   return (@returnStatus)
496   
497           end -- } 	-- Report action 
498   
499           -- If no row in #spaceusageinfo, no object qualified and hence report an
500           -- error message indicating the same and return back.
501           --
502           if not exists (select 1 from #spaceusageinfo)
503           begin
504               declare @tmpname varchar(290)
505                   , @tmpdbname varchar(30)
506   
507               select @tmpname = @uname + "." + @tname
508                   , @tmpdbname = db_name()
509   
510               if @entity_type = @entity_index
511                   select @tmpname = @tmpname + "." + @iname
512   
513               raiserror 19531, @entity_type, @tmpname, @tmpdbname
514               return (7)
515           end
516           return (0)
517   
518       end -- }	-- }
519   


exec sp_procxmode 'sp_spaceusage_object_init', 'AnyMode'
go

Grant Execute on sp_spaceusage_object_init to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 290
 QJWI 5 Join or Sarg Without Index 297
 MTYP 4 Assignment type mismatch @callerID: varchar(30) = varchar(40) 492
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
295
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 143
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 157
 QTYP 4 Comparison type mismatch Comparison type mismatch: uint vs int 279
 QTYP 4 Comparison type mismatch Comparison type mismatch: uint vs int 280
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 129
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 159
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 177
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 179
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 181
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 186
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 282
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 282
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 283
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 283
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 290
 MAW1 3 Warning message on %name% sybsystemprocs..systabstats.id: Warning message on systabstats 290
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 297
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 297
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_object_init  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..syspartitions  
 MGTP 3 Grant to public sybsystemprocs..systabstats  
 MNER 3 No Error Check should check return value of exec 77
 MNER 3 No Error Check should check return value of exec 78
 MUCO 3 Useless Code Useless Brackets in create proc 32
 MUCO 3 Useless Code Useless Begin-End Pair 44
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 303
 MUCO 3 Useless Code Useless Brackets 304
 MUCO 3 Useless Code Useless Brackets 383
 MUCO 3 Useless Code Useless Brackets 384
 MUCO 3 Useless Code Useless Brackets 495
 MUCO 3 Useless Code Useless Brackets 514
 MUCO 3 Useless Code Useless Brackets 516
 QNAO 3 Not using ANSI Outer Join 251
 QNUA 3 Should use Alias: Column fill_factor should use alias i 200
 QNUA 3 Should use Alias: Column maxrowsperpage should use alias i 201
 QNUA 3 Should use Alias: Column exp_rowsize should use alias i 202
 QNUA 3 Should use Alias: Column res_page_gap should use alias i 203
 QNUA 3 Should use Alias: Column type should use alias o 270
 QPNC 3 No column in condition 260
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
Uncovered: [uid]
263
 VNRD 3 Variable is not read @entity_table 67
 VNRD 3 Variable is not read @esdnumstr 77
 VNRD 3 Variable is not read @ebfnumstr 78
 VUNU 3 Variable is not used @msg 63
 MSUB 2 Subquery Marker 297
 MSUC 2 Correlated Subquery Marker 261
 MTR1 2 Metrics: Comments Ratio Comments: 35% 31
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 11dec - 5exi + 2 31
 MTR3 2 Metrics: Query Complexity Complexity: 78 31
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, o2=sybsystemprocs..sysobjects} 0 261

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#spaceusageinfo (1) 
reads table sybsystemprocs..syspartitions  
reads table sybsystemprocs..sysindexes  
calls proc sybsystemprocs..sp_exec_SQL  
   writes table sybsystemprocs..sp_exec_SQL_rset_001 
calls proc sybsystemprocs..sp_versioncrack  
   calls proc sybsystemprocs..sp_split_string  
reads table sybsystemprocs..systabstats  
reads table sybsystemprocs..sysobjects  

CALLERS
called by proc sybsystemprocs..sp_spaceusage_object  
   called by proc sybsystemprocs..sp_spaceusage