DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_object_archdata  31 Aug 14Defects Dependencies

1     
2     /*
3     **	SP_SPACEUSAGE_OBJECT_ARCHDATA
4     **
5     **	The sub-procedure that saves the space usage information in the
6     **	temporary table #spaceusageinfo into an archive table. This is
7     **	applicable only in case of "archive" action. It is possible to apply a
8     **	WHERE clause, if any, to selectively archive only the data of interest.
9     **	Called by sp_spaceusage_object.
10    **
11    **	Parameters
12    **		@archivetabname	- The 3-part name of the archive table.
13    **		@where_clause	- WHERE clause, if any, to select the data.
14    **		@entity_type	- Type of entity.
15    **		@iname		- Index name.
16    **
17    **	Note:	The @entity_type and @iname are passed because for entity type
18    **		"index" the #spaceusageinfo table has and adtitional data layer
19    **		row that qualifies even though they may not qualify (they may
20    **		qualify is the index name pattern included them) and are not
21    **		expected in the output result. They need to be filtered out
22    **		using the WHERE clause.
23    **
24    **	Returns
25    **		0 - if all goes well
26    **	    other - error while execution 
27    {
28    */
29    create procedure sp_spaceusage_object_archdata
30    (
31        @archivetabname varchar(320)
32        , @where_clause varchar(1536)
33        , @entity_type varchar(12)
34        , @iname varchar(255)
35    )
36    as
37        begin -- {	
38    
39            declare @entity_table varchar(6)
40                , @entity_index varchar(6)
41    
42                , @sqlstmt varchar(2600)
43                , @returnStatus int
44                , @colsToArchive varchar(660)
45                , @whoami varchar(50)
46                , @msg varchar(256)
47    
48            select @whoami = "sp_spaceusage_object_archdata"
49    
50                , @entity_table = "table"
51                , @entity_index = "index"
52    
53            if @entity_type = @entity_index
54            begin
55                select @where_clause = @where_clause + case @where_clause
56                        when NULL
57                        then " WHERE "
58                        else " AND "
59                    end
60                    + " IndexName LIKE '" + @iname + "'"
61            end
62    
63            -- We do not want to archive all the columns, as some of them are 
64            -- temporary placeholders for intermediate data and are not meant for 
65            -- archiving. Hence, we explicitly select all the columns to archive.
66            --
67            select @colsToArchive = "  VersionNum, ESDNum, EBFNum"
68                + ", ArchiveDateTime"
69                + ", ServerName, MaxPageSize"
70                + ", DBName, OwnerName, TableName"
71                + ", Id, IndId, IndexName, PtnId, PtnName"
72                + ", DataPtnId, RowSize, NumRows, RowCount_ts"
73                + ", NumFwdRows, NumDelRows, EmptyPageCount"
74                + ", DataPageCount, UsedPageCount"
75                + ", RsvdPageCount, SpUtil, DPCR, DRCR, IPCR"
76                + ", LGIO, FF, MRPP, ERS, RPG, LeafPageCount"
77                + ", IndexHeight, OAMAPageCount"
78                + ", Extent0PageCount, Status, Sysstat"
79                + ", Sysstat2, NonLeafRowSize, ExpIndexHeight"
80                + ", ExpDataPageCount"
81                + ", ExpUsedPageCount, ExpRsvdPageCount"
82                + ", ExpLeafPageCount, PctBloatUsedPages"
83                + ", PctBloatRsvdPages, PctBloatLeafPages"
84                + ", ExtentUtil, PctEmptyPages, PctFwdRows"
85                + ", LockScheme, NumVarCols, HasAPLCI"
86                + ", StatModDate"
87    
88            /* 
89            ** If archive table does not exist, create it by SELECTing
90            ** #spaceusageinfo  INTO int it. Else, INSERT #spaceusageinfo into it.
91            */
92            if object_id(@archivetabname) is NULL
93            begin
94                select @sqlstmt = " SELECT " + @colsToArchive
95                    + " INTO " + @archivetabname
96                    + " FROM 	#spaceusageinfo "
97                    + @where_clause
98            end
99            else
100           begin
101               select @sqlstmt = " INSERT INTO " + @archivetabname
102                   + " SELECT " + @colsToArchive
103                   + " FROM 	     #spaceusageinfo "
104                   + @where_clause
105           end
106   
107           exec @returnStatus = sp_exec_SQL @sqlstmt, @whoami
108   
109           if @returnStatus != 0
110           begin
111               return (@returnStatus)
112           end
113           else
114           begin
115               exec sp_getmessage 19535, @msg out
116               print @msg, @archivetabname
117               return (0)
118           end
119   
120       end -- }	-- }
121   


exec sp_procxmode 'sp_spaceusage_object_archdata', 'AnyMode'
go

Grant Execute on sp_spaceusage_object_archdata to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @callerID: varchar(30) = varchar(50) 107
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_object_archdata  
 MNER 3 No Error Check should check return value of exec 115
 MUCO 3 Useless Code Useless Brackets in create proc 30
 MUCO 3 Useless Code Useless Begin-End Pair 37
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 117
 VNRD 3 Variable is not read @entity_table 50
 MTR1 2 Metrics: Comments Ratio Comments: 38% 29
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 2 = 3dec - 3exi + 2 29
 MTR3 2 Metrics: Query Complexity Complexity: 18 29

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
calls proc sybsystemprocs..sp_exec_SQL  

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