DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_object  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     **	SP_SPACEUSAGE_OBJECT
4     **
5     **	The driver procedure for the getting space usage information for entity
6     **	types - "table" and "index". Called by sp_spaceusage.
7     **
8     **	Parameters:
9     **		@actionword	- The action to be performed. 
10    **		@unit		- Unit for displaying page counts, if any.
11    **		@archive_db	- Name of the database where the archive table
12    **				  resides or will be created.
13    **		@archive_prefix - Prefix for the archive table name, if any.
14    **		@entity_type	- Type of entity. Can be "table"/"index".
15    **		@nouname	- Was no owner name provided by the user?
16    **		@uname		- Owner/user name.
17    **		@tname		- Table name.
18    **		@iname		- Index name.
19    **		@select_list	- Comma separated list of columns to project
20    **				  the output.
21    **		@where_clause	- A WHERE clause, if any, to filter the output.
22    **		@order_by_clause- An ORDER BY clause, to order the output.
23    **		@from_date	- Date, if any, from when on to look for data 
24    **				  in the archive table to generate report.
25    **		@to_date	- Date, if any, till when to look for data in 
26    **				  the archive table to generate report.
27    **	
28    **	Returns
29    **		0 - if all goes well
30    **		6 - archive table not found
31    **	    other - error while execution 
32    {
33    */
34    create or replace procedure sp_spaceusage_object
35    (
36        @actionword varchar(20)
37        , @unit varchar(6) = NULL
38        , @archive_db varchar(30) = NULL
39        , @archive_prefix varchar(238) = NULL
40        , @entity_type varchar(12)
41        , @nouname bit
42        , @uname varchar(255)
43        , @tname varchar(255)
44        , @iname varchar(255)
45        , @select_list varchar(1636) = NULL
46        , @where_clause varchar(1536) = NULL
47        , @order_by_clause varchar(768) = NULL
48        , @from_date varchar(30) = NULL
49        , @to_date varchar(30) = NULL
50    
51    )
52    as
53        begin -- { 	
54    
55            declare
56                @returnStatus int
57                , @archivetabname varchar(320)
58                , @defaultarchivetabname varchar(30)
59    
60                -- String constants for the entity type
61                , @entity_table varchar(6)
62                , @entity_index varchar(6)
63    
64                -- String constants for the action 
65                , @action_display varchar(8)
66                , @action_display_summary varchar(16)
67                , @action_report varchar(7)
68                , @action_report_summary varchar(15)
69                , @action_archive varchar(8)
70    
71            select
72                @archivetabname = NULL
73                , @defaultarchivetabname = "spaceusage_object"
74    
75                , @entity_table = "table"
76                , @entity_index = "index"
77    
78                , @action_display = "display"
79                , @action_display_summary = "display summary"
80                , @action_report = "report"
81                , @action_report_summary = "report summary"
82                , @action_archive = "archive"
83    
84            if @actionword not in (@action_display, @action_display_summary)
85            begin -- This is report/archive mode
86    
87                -- Verify that the archive database does exist.
88                if not exists (select 1
89                        from master.dbo.sysdatabases
90                        where name like @archive_db)
91                begin
92                    raiserror 18322, @archive_db
93                    return (6)
94                end
95    
96                select @archivetabname = @archive_db + "." + user_name() + "."
97                    + case @archive_prefix
98                        when NULL
99                        then @defaultarchivetabname
100                       else @archive_prefix
101                           + @defaultarchivetabname
102                   end
103   
104               -- Verify the archive table does exist in the database for the
105               -- report action.
106               -- 
107               if @actionword in (@action_report
108                       , @action_report_summary)
109                   and object_id(@archivetabname) is NULL
110               begin
111                   declare @tmpname varchar(290)
112   
113                   select @tmpname = user_name() + "."
114                       + case @archive_prefix
115                           when NULL
116                           then @defaultarchivetabname
117                           else @archive_prefix
118                               + @defaultarchivetabname
119                       end
120   
121                   raiserror 17870, @tmpname, @archive_db, NULL
122                   return (6)
123               end
124           end
125   
126           /* 
127           ** Create and initialize the temporary #spaceusageinfo table based on 
128           ** the action specified. For 'display'/'archive' action, the table is 
129           ** created by joining with system tables. For 'report' action, it is 
130           ** generated by selecting into it the relavant data from the archive 
131           ** table.
132           **
133           ** NOTE: Any change done to this table needs a change in the
134           ** corresponding #spaceusageinfo table created at the beginning of the
135           ** file (at line #15). And the inserts that are done in the procedure
136           ** sp_spaceusage_object_init (at lines #147, 284, 375).
137           */
138           create table #spaceusageinfo
139           (
140               VersionNum int NOT NULL
141               , ESDNum int NOT NULL
142               , EBFNum int NOT NULL
143               , ArchiveDateTime datetime NOT NULL
144               , ServerName varchar(255) NULL
145               , MaxPageSize int NOT NULL
146               , DBName varchar(30) NOT NULL
147               , OwnerName varchar(30) NOT NULL
148               , TableName varchar(255) NOT NULL
149               , Id int NOT NULL
150               , IndId smallint NOT NULL
151               , IndexName varchar(255) NULL
152               , PtnId int NOT NULL
153               , PtnName varchar(255) NOT NULL
154               , DataPtnId int NOT NULL
155               , RowSize float NULL
156               , NumRows float NULL
157               , RowCount_ts float NULL
158               , NumFwdRows float NULL
159               , NumDelRows float NULL
160               , EmptyPageCount int NULL
161               , DataPageCount int NULL
162               , UsedPageCount int NULL
163               , RsvdPageCount int NULL
164               , SpUtil decimal(9, 2) NULL
165               , DPCR decimal(5, 4) NULL
166               , DRCR decimal(5, 4) NULL
167               , IPCR decimal(5, 4) NULL
168               , LGIO decimal(5, 4) NULL
169               , FF smallint NULL
170               , MRPP smallint NULL
171               , ERS smallint NULL
172               , RPG smallint NULL
173               , LeafPageCount int NULL
174               , IndexHeight smallint NULL
175               , OAMAPageCount int NULL
176               , Extent0PageCount int NULL
177               , Status int NULL
178               , Sysstat smallint NOT NULL
179               , Sysstat2 int NOT NULL
180               , NonLeafRowSize decimal(10, 4) NULL
181               , ExpIndexHeight smallint NULL
182               , ExpDataPageCount int NULL
183               , ExpUsedPageCount int NULL
184               , ExpRsvdPageCount int NULL
185               , ExpLeafPageCount int NULL
186               , PctBloatUsedPages decimal(14, 2) NULL
187               , PctBloatRsvdPages decimal(14, 2) NULL
188               , PctBloatLeafPages decimal(14, 2) NULL
189               , ExtentUtil decimal(5, 2) NULL
190               , PctEmptyPages decimal(5, 2) NULL
191               , PctFwdRows decimal(5, 2) NULL
192               , LockScheme tinyint NOT NULL
193               , NumVarCols smallint NOT NULL
194               , HasAPLCI bit NOT NULL
195               , StatModDate date NULL
196               , SpacePerPage float NULL
197               , CalcRowsPerPage float NULL
198               , EmptyPages float NULL
199               , DataPages float NULL
200               , UsedPages float NULL
201               , RsvdPages float NULL
202               , LeafPages float NULL
203               , ExpDataPages float NULL
204               , ExpUsedPages float NULL
205               , ExpRsvdPages float NULL
206               , ExpLeafPages float NULL
207           )
208   
209           if (@@error != 0)
210               return (@@error)
211   
212           exec @returnStatus = sp_spaceusage_object_init @actionword
213               , @nouname
214               , @uname
215               , @tname
216               , @iname
217               , @entity_type
218               , @archivetabname
219               , @from_date
220               , @to_date
221   
222           if @returnStatus != 0
223               return (@returnStatus)
224   
225           /* 
226           ** Populate the #spaceusageinfo table that was created and initialized 
227           ** only with raw data in case of display/archive action.
228           */
229           if @actionword in (@action_display
230                   , @action_display_summary
231                   , @action_archive)
232           begin -- {	
233   
234               exec @returnStatus = sp_spaceusage_object_populate
235   
236               if @returnStatus != 0
237                   return (@returnStatus)
238   
239           end -- } 
240   
241           /*
242           ** Depending on the unit specifier, convert the page counts into the
243           ** appropriate data type before display.
244           **
245           ** NOTE: Although, archive mode ideally would only archive the value
246           ** in pages, it may have a WHERE clause on these computed columns for
247           ** which it requires these converted values.
248           */
249           exec @returnStatus = sp_spaceusage_cnvtounit @unit, "#spaceusageinfo"
250   
251           if @returnStatus != 0
252               return (@returnStatus)
253   
254           /* 
255           ** Now, display/archive the information collected and computed to the 
256           ** user based on the action. For display/report the user expects to see
257           ** the output on screen. And for archive action the user expects the 
258           ** output to be appended to the archive table.
259           */
260           if @actionword in (@action_display
261                   , @action_display_summary
262                   , @action_report
263                   , @action_report_summary)
264           begin -- {
265   
266               exec @returnStatus = sp_spaceusage_object_genoutput
267                   @actionword
268                   , @unit
269                   , @entity_type
270                   , @iname
271                   , @select_list
272                   , @where_clause
273                   , @order_by_clause
274   
275               if @returnStatus != 0
276                   return (@returnStatus)
277   
278           end -- }
279           else if @actionword = @action_archive
280           begin -- {
281   
282               exec @returnStatus = sp_spaceusage_object_archdata
283                   @archivetabname
284                   , @where_clause
285                   , @entity_type
286                   , @iname
287   
288               if @returnStatus != 0
289                   return (@returnStatus)
290   
291           end -- }
292   
293           drop table #spaceusageinfo
294   
295       end -- }	-- }
296   


exec sp_procxmode 'sp_spaceusage_object', 'AnyMode'
go

Grant Execute on sp_spaceusage_object to public
go
DEFECTS
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_object  
 MUCO 3 Useless Code Useless Brackets in create proc 35
 MUCO 3 Useless Code Useless Begin-End Pair 53
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 209
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 237
 MUCO 3 Useless Code Useless Brackets 252
 MUCO 3 Useless Code Useless Brackets 276
 MUCO 3 Useless Code Useless Brackets 289
 VNRD 3 Variable is not read @entity_table 75
 VNRD 3 Variable is not read @entity_index 76
 MSUB 2 Subquery Marker 88
 MTR1 2 Metrics: Comments Ratio Comments: 32% 34
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 14dec - 6exi + 2 34
 MTR3 2 Metrics: Query Complexity Complexity: 51 34

DATA PROPAGATION detailed
ColumnWritten To
@archive_dbsp_exec_SQL_rset_001.sqlbNoName57
@archive_prefixsp_exec_SQL_rset_001.sqlbNoName57
@from_datesp_exec_SQL_rset_001.sqlbNoName57
@inamesp_autoformat_rset_002._clid#af sp_exec_SQL_rset_001.sqlbNoName57
@nounamesp_exec_SQL_rset_001.sqlbNoName57
@select_listsp_autoformat_rset_001._maxlen#af °._asname#af sp_autoformat_rset_002._clid#af sp_autoformat_rset_003._asname#af
@tnamesp_exec_SQL_rset_001.sqlbNoName57
@to_datesp_exec_SQL_rset_001.sqlbNoName57
@unamesp_exec_SQL_rset_001.sqlbNoName57
@where_clausesp_autoformat_rset_002._clid#af sp_exec_SQL_rset_001.sqlbNoName57

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#spaceusageinfo (1) 
calls proc sybsystemprocs..sp_spaceusage_cnvtounit  
   calls proc sybsystemprocs..sp_exec_SQL  
      writes table sybsystemprocs..sp_exec_SQL_rset_001 
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_spaceusage_object_init  
   reads table sybsystemprocs..sysobjects  
   reads table sybsystemprocs..systabstats  
   reads table sybsystemprocs..syspartitions  
   calls proc sybsystemprocs..sp_versioncrack  
      calls proc sybsystemprocs..sp_split_string  
   calls proc sybsystemprocs..sp_exec_SQL  
   read_writes table tempdb..#spaceusageinfo (1) 
   reads table sybsystemprocs..sysindexes  
calls proc sybsystemprocs..sp_spaceusage_object_genoutput  
   reads table tempdb..#spaceusageinfo (1) 
   calls proc sybsystemprocs..sp_getmessage  
      reads table master..syslanguages (1)  
      calls proc sybsystemprocs..sp_validlang  
         reads table master..syslanguages (1)  
      reads table master..sysmessages (1)  
      reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_autoformat  
      writes table sybsystemprocs..sp_autoformat_rset_003 
      reads table tempdb..syscolumns (1)  
      read_writes table tempdb..#colinfo_af (1) 
      writes table sybsystemprocs..sp_autoformat_rset_001 
      reads table tempdb..systypes (1)  
      reads table master..systypes (1)  
      writes table sybsystemprocs..sp_autoformat_rset_002 
      writes table sybsystemprocs..sp_autoformat_rset_005 
      calls proc sybsystemprocs..sp_autoformat  
      calls proc sybsystemprocs..sp_namecrack  
      writes table sybsystemprocs..sp_autoformat_rset_004 
      reads table master..syscolumns (1)  
   writes table tempdb..#summary1 (1) 
   writes table tempdb..#summary2 (1) 
   calls proc sybsystemprocs..sp_exec_SQL  
calls proc sybsystemprocs..sp_spaceusage_object_populate  
   read_writes table tempdb..#indexData (1) 
   calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_index_row_size_est  
      reads table sybsystemprocs..syscolumns  
      reads table sybsystemprocs..sysindexes  
      reads table sybsystemprocs..sysobjects  
   reads table sybsystemprocs..syscolumns  
   calls proc sybsystemprocs..sp_aux_checkroleperm  
      reads table master..sysconfigures (1)  
      reads table master..syscurconfigs (1)  
   read_writes table tempdb..#spaceusageinfo (1) 
   reads table master..sysconfigures (1)  
   calls proc sybsystemprocs..sp_index_space_est  
      reads table master..syscurconfigs (1)  
      read_writes table tempdb..#indexData (1) 
      reads table sybsystemprocs..sysindexes  
calls proc sybsystemprocs..sp_spaceusage_object_archdata  
   calls proc sybsystemprocs..sp_exec_SQL  
   calls proc sybsystemprocs..sp_getmessage  

CALLERS
called by proc sybsystemprocs..sp_spaceusage