1
2 /*
3 ** Generated by spgenmsgs.pl on Mon Nov 20 03:34:15 2006
4 */
5 /*
6 ** raiserror Messages for spaceusage [Total 4]
7 **
8 ** 17993, "'%1!' is not a valid argument."
9 ** 19194, "Argument '%1!' is either invalid or non-unique. Valid arguments are: %2!"
10 ** 19491, "Archive prefix name '%1!' cannot exceed %2! bytes as this can cause the extended name of the archive table to be an invalid identifier."
11 ** 19530, "Argument '%1!' is invalid with argument '%2!'. Type '%3!' for usage information."
12 */
13 /*
14 ** sp_getmessage Messages for spaceusage [Total 0]
15 */
16 /*
17 ** End spgenmsgs.pl output.
18 */
19 /*
20 ** SP_SPACEUSAGE
21 **
22 ** Driver interface to get space usage information on different types of
23 ** enitities. This will in turn call the entity and action specific
24 ** sub-procedures.
25 **
26 ** Parameters
27 ** @action - The action to be performed. The using_clause, if any,
28 ** is part of this string. The real action keyword is
29 ** extracted from this string.
30 ** @arg1 - This is:
31 ** o action word on which help is sought, if @action =
32 ** "help".
33 ** o entity type string.
34 ** @arg2 - This is:
35 ** o entity type string, if @action = "help".
36 ** o entity name identifier pattern string.
37 ** @optarg1- This optional argument can be:
38 ** o SELECT list to project the result set for 'display'
39 ** or 'report' action word.
40 ** o WHERE clause to filter the result set otherwise.
41 ** @optarg2- This optional argument can be:
42 ** o WHERE clause to filter the result set for 'display'
43 ** or 'report' action word.
44 ** o ORDER BY clause to order the result set for
45 ** 'display summary' or 'report summary' action word.
46 ** o command to run on the table for 'archive' action
47 ** word.
48 ** @optarg3- This optional argument can be:
49 ** o ORDER BY clause to order the result set for
50 ** 'display' or 'report' action word.
51 ** o command to run on the table for 'display summary'
52 ** action word.
53 ** o archive date from when on to get data for 'report
54 ** summary' action word.
55 ** o ignored otherwise.
56 ** @optarg4- This optional argument can be:
57 ** o command to run on the table for 'display' action
58 ** word.
59 ** o archive date from when on to get data for 'report'
60 ** action word.
61 ** o archive date till when to get data for 'report
62 ** summary' action word.
63 ** o ignored otherwise.
64 ** @optarg5- This optional argument can be:
65 ** o archive date till when to get data for 'report'
66 ** action word.
67 ** o ignored otherwise.
68 **
69 ** Returns
70 ** 0 - if all goes well
71 ** 1 - invalid action
72 ** 2 - invalid entity type
73 ** 3 - invalid entity name
74 ** 4 - invalid command
75 ** 5 - invalid USING clause
76 ** 6 - archive table not found
77 ** 7 - invalid datetime format [from_date/to_date]
78 ** 8 - invalid SELECT list, WHERE clause or ORDER BY clause
79 ** other - error while execution
80 {
81 */
82 create procedure sp_spaceusage
83 (
84 @action varchar(350) = NULL
85 , @arg1 varchar(20) = NULL
86 , @arg2 varchar(550) = NULL
87 , @optarg1 varchar(1536) = NULL
88 , @optarg2 varchar(1536) = NULL
89 , @optarg3 varchar(768) = NULL
90 , @optarg4 varchar(100) = NULL
91 , @optarg5 varchar(30) = NULL
92 )
93 as
94 begin -- {
95
96 declare
97 @entity_type varchar(12)
98 , @entity_name varchar(550)
99 , @select_list varchar(1536)
100 , @where_clause varchar(1536)
101 , @orderby_clause varchar(768)
102 , @command varchar(100)
103 , @from_date varchar(30)
104 , @to_date varchar(30)
105
106 , @actionword varchar(20)
107 , @action_help varchar(5)
108 , @action_archive varchar(10)
109 , @action_display varchar(10)
110 , @action_display_summary varchar(20)
111 , @action_report varchar(10)
112 , @action_report_summary varchar(20)
113
114 , @uname varchar(255)
115 , @tname varchar(255)
116 , @iname varchar(255)
117 , @tmpname varchar(255)
118 , @tmpname2 varchar(255)
119 , @archive_db varchar(30)
120 , @archive_prefix varchar(255)
121 , @unit varchar(6)
122 , @maxtabnamelen int
123 , @maxtranlogprefix int
124 , @maxobjectprefix int
125
126 , @entity_table varchar(6)
127 , @entity_index varchar(6)
128 , @entity_tranlog varchar(8)
129 , @entity_database varchar(9)
130 , @valid_actions varchar(75)
131 , @valid_entity_types varchar(30)
132
133 , @archive_table_name varchar(320)
134 , @using_clause varchar(350)
135 , @left_substr varchar(350)
136 , @right_substr varchar(350)
137 , @usingIndex int
138 , @returnStatus int
139 , @commaPos int
140 , @nouname bit
141
142 , @msg varchar(256)
143
144 select
145 @action_help = "help"
146 , @action_archive = "archive"
147 , @action_display = "display"
148 , @action_display_summary = "display summary"
149 , @action_report = "report"
150 , @action_report_summary = "report summary"
151
152 , @entity_table = "table"
153 , @entity_index = "index"
154 , @entity_tranlog = "tranlog"
155
156 , @usingIndex = 0
157 , @unit = "KB"
158 , @archive_db = db_name()
159 , @nouname = 0
160
161 /* Get the maximum length of a table name. */
162 select @maxtabnamelen = c.length
163 from master..syscolumns c
164 where c.id = object_id('sysobjects')
165 and c.name = 'name'
166
167 /* Get the maximum length allowed for the prefizx strenth. */
168 select @maxtranlogprefix = @maxtabnamelen
169 - datalength("spaceusage_tranlog")
170 , @maxobjectprefix = @maxtabnamelen
171 - datalength("spaceusage_object")
172
173 select
174 @valid_entity_types = "'" + @entity_table
175 + "', '" + @entity_index
176 + "', '" + @entity_tranlog
177 + "'"
178
179 , @valid_actions = "'" + @action_help
180 + "', '" + @action_archive
181 + "', '" + @action_display
182 + "', '" + @action_display_summary
183 + "', '" + @action_report
184 + "', '" + @action_report_summary
185 + "'"
186
187 /* Extract the action word from the input action string. */
188 if @action is NULL
189 begin
190 select @actionword = @action_help
191 end
192 else
193 begin
194 select @usingIndex = charindex("USING", upper(@action))
195
196 select @actionword = case @usingIndex
197 when 0 then @action
198 else substring(@action, 1,
199 @usingIndex - 1)
200 end
201
202 select @actionword = lower(ltrim(rtrim(@actionword)))
203 end
204
205 /* Extract the USING clause from the input action string. */
206 if @usingIndex = 0
207 select @using_clause = NULL
208 else
209 begin
210 exec sp_split_string @action, "USING", 0, @left_substr out
211 , @right_substr out
212
213 select @using_clause = ltrim(rtrim(@right_substr))
214 end
215
216 if @actionword not in (@action_help
217 , @action_archive
218 , @action_display
219 , @action_display_summary
220 , @action_report
221 , @action_report_summary)
222 begin
223 raiserror 19194, @actionword, @valid_actions
224 return (1)
225 end
226
227 if @actionword = @action_help
228 begin
229 exec sp_spaceusage_showhelp @arg1, @arg2
230 , @valid_actions
231 , @valid_entity_types
232 return (0)
233 end
234
235 select @entity_type = lower(ltrim(rtrim(@arg1)))
236 , @entity_name = @arg2
237
238 if @entity_type not in (@entity_table
239 , @entity_index
240 , @entity_tranlog)
241 begin
242 raiserror 19194, @entity_type, @valid_entity_types
243 return (2)
244 end
245
246 if @entity_type in (@entity_table, @entity_index)
247 begin -- { -- Index/Table begins
248
249 if @actionword not in (@action_archive
250 , @action_display
251 , @action_display_summary
252 , @action_report
253 , @action_report_summary)
254 begin
255 raiserror 19530, @actionword, @entity_type, "sp_spaceusage [help]"
256 return (1)
257 end
258
259 if @actionword = @action_display
260 begin
261 select @select_list = @optarg1
262 , @where_clause = @optarg2
263 , @orderby_clause = @optarg3
264 , @command = @optarg4
265 end
266 else if @actionword = @action_display_summary
267 begin
268 select @where_clause = @optarg1
269 , @orderby_clause = @optarg2
270 , @command = @optarg3
271 end
272 else if @actionword = @action_report
273 begin
274 select @select_list = @optarg1
275 , @where_clause = @optarg2
276 , @orderby_clause = @optarg3
277 , @from_date = @optarg4
278 , @to_date = @optarg5
279 end
280 else if @actionword = @action_report_summary
281 begin
282 select @where_clause = @optarg1
283 , @orderby_clause = @optarg2
284 , @from_date = @optarg3
285 , @to_date = @optarg4
286 end
287 else if @actionword = @action_archive
288 begin
289 select @where_clause = @optarg1
290 , @command = @optarg2
291 end
292
293 exec @returnStatus = sp_spaceusage_paramcheck
294 @actionword
295 , @entity_type
296 , @entity_name
297 , @using_clause
298 , @command
299 , @from_date
300 , @to_date
301 , @select_list
302 , @where_clause
303 , @orderby_clause
304
305 if @returnStatus != 0
306 return (@returnStatus)
307
308 /*
309 ** Extract the owner name, object name and index name pattern
310 ** from the entity name string.
311 */
312 if @entity_type = @entity_index
313 begin
314
315 -- The name would be 3-part or 2-part, i.e., either
316 -- 'owner.table.index' or '.table.index' or
317 -- 'table.index'.
318 --
319 exec sp_namecrack @entity_name, @tmpname out
320 , @uname out
321 , @tname out
322 , @iname out
323
324 if @uname is NULL
325 begin
326 select @uname = user_name()
327 , @nouname = 1
328 end
329
330 if @tname is NULL or @iname is NULL
331 begin
332 raiserror 17993, @entity_name
333 return (3)
334 end
335
336 end
337 else -- entity type is 'table'
338 begin
339
340 -- The name would be 2-part or 1-part, i.e., either
341 -- 'owner.table' or '.table' or 'table'.
342 --
343 exec sp_namecrack @entity_name, @tmpname out
344 , @tmpname2 out
345 , @uname out
346 , @tname out
347
348 if @uname is NULL
349 begin
350 select @uname = user_name()
351 , @nouname = 1
352 end
353
354 if @tname is NULL
355 begin
356 raiserror 17993, @entity_name
357 return (3)
358 end
359
360 select @iname = "%"
361
362 end
363
364 /* Run the command, if any, for the display/archive action. */
365 if @actionword in (@action_archive
366 , @action_display
367 , @action_display_summary)
368 begin
369 if @command is not NULL
370 begin
371 exec @returnStatus = sp_spaceusage_runcmd
372 @uname
373 , @tname
374 , @command
375 , 1
376
377 if @returnStatus != 0
378 return (@returnStatus)
379
380 end
381 end
382
383 /*
384 ** Extract the archive database and/or the prefix string from
385 ** the using_clause, if any, for the archive/report action, and
386 ** the unit specifier, if any.
387 */
388 if @using_clause is not NULL
389 begin
390 exec @returnStatus = sp_spaceusage_processusing
391 @using_clause
392 , @archive_db out
393 , @archive_prefix out
394 , @unit out
395
396 if @returnStatus != 0
397 return (@returnStatus)
398
399 if @archive_prefix is not NULL
400 and datalength(@archive_prefix) > @maxobjectprefix
401 begin
402 raiserror 19491, @archive_prefix, @maxobjectprefix
403 return (5)
404 end
405
406 if @archive_db is NULL
407 select @archive_db = db_name()
408
409 if @unit is NULL
410 select @unit = "KB"
411
412 end
413
414 exec @returnStatus = sp_spaceusage_object
415 @actionword
416 , @unit
417 , @archive_db
418 , @archive_prefix
419 , @entity_type
420 , @nouname
421 , @uname
422 , @tname
423 , @iname
424 , @select_list
425 , @where_clause
426 , @orderby_clause
427 , @from_date
428 , @to_date
429
430 if @returnStatus != 0
431 return (@returnStatus)
432
433 end -- } -- Index/Table done!
434
435 else if @entity_type = @entity_tranlog
436
437 begin -- { -- Transaction log begins
438
439 if @actionword not in (@action_archive
440 , @action_display
441 , @action_report)
442 begin
443 raiserror 19530, @actionword, @entity_type, "sp_spaceusage [help]"
444 return (1)
445 end
446
447 if @actionword = @action_display
448 begin
449 select @select_list = @optarg1
450 , @where_clause = @optarg2
451 , @orderby_clause = @optarg3
452 end
453 else if @actionword = @action_report
454 begin
455 select @select_list = @optarg1
456 , @where_clause = @optarg2
457 , @orderby_clause = @optarg3
458 , @from_date = @optarg4
459 , @to_date = @optarg5
460 end
461 else if @actionword = @action_archive
462 begin
463 select @where_clause = @optarg1
464 end
465
466 exec @returnStatus = sp_spaceusage_paramcheck
467 @actionword
468 , @entity_type
469 , @entity_name
470 , @using_clause
471 , NULL
472 , @from_date
473 , @to_date
474 , @select_list
475 , @where_clause
476 , @orderby_clause
477
478 if @returnStatus != 0
479 return (@returnStatus)
480
481 /*
482 ** Extract the archive database and/or the prefix string from
483 ** the using_clause, if any, for the archive/report action, and
484 ** the unit specifier, if any.
485 */
486 if @using_clause is not NULL
487 begin
488 exec @returnStatus = sp_spaceusage_processusing
489 @using_clause
490 , @archive_db out
491 , @archive_prefix out
492 , @unit out
493
494 if @returnStatus != 0
495 return (@returnStatus)
496
497 if @archive_prefix is not NULL
498 and datalength(@archive_prefix) > @maxtranlogprefix
499 begin
500 raiserror 19491, @archive_prefix, @maxtranlogprefix
501 return (5)
502 end
503
504 if @archive_db is NULL
505 select @archive_db = db_name()
506
507 if @unit is NULL
508 select @unit = "KB"
509
510 end
511
512 exec @returnStatus = sp_spaceusage_tranlog
513 @actionword
514 , @unit
515 , @archive_db
516 , @archive_prefix
517 , @select_list
518 , @where_clause
519 , @orderby_clause
520 , @from_date
521 , @to_date
522
523 if @returnStatus != 0
524 return (@returnStatus)
525
526 end -- } -- Transaction log done!
527
528 return (0)
529
530 end -- } -- }
531
exec sp_procxmode 'sp_spaceusage', 'AnyMode'
go
Grant Execute on sp_spaceusage to public
go
