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 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(7, 2) NULL
187 , PctBloatRsvdPages decimal(7, 2) NULL
188 , PctBloatLeafPages decimal(7, 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
MGTP 3 Grant to public master..sysdatabases
MSUB 2 Subquery Marker 
