1
2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3 /* 4.8 1.1 06/14/90 sproc/src/reportstats */
4
5 /*
6 ** This stored procedure prints out Chargeback Accounting Statistics and
7 ** percentages.
8 */
9
10 /*
11 ** Messages for "sp_reportstats" 17935
12 **
13 ** 17231, "No login with the specified name exists."
14 **
15 ** NOTE: We need to LOCALIZE the column names of the SELECTs below!!
16 **
17 */
18
19 create procedure sp_reportstats
20 @loginame varchar(255) = NULL /* name of login to report */
21 as
22
23 declare @cnt int,
24 @cputot float,
25 @iotot float
26
27
28 if @@trancount = 0
29 begin
30 set chained off
31 end
32
33 set transaction isolation level 1
34
35 /* check if user has sa role, proc_role will also do auditing
36 ** if required. proc_role will also print error message if required.
37 */
38
39 if (proc_role("sa_role") = 0)
40 return (1)
41
42 begin transaction
43
44 /*
45 ** Get totals for all logins except sa.
46 ** If the total is zero all users have
47 ** used zero time so far. Change totals
48 ** to random value (1) and go on.
49 ** % usage calculations will appropriately
50 ** result in zero this way.
51 */
52 select @cputot = sum(convert(float, totcpu)),
53 @iotot = sum(convert(float, totio))
54 from master.dbo.syslogins holdlock
55
56 if @cputot = 0
57 select @cputot = 1
58
59 if @iotot = 0
60 select @iotot = 1
61
62 if @loginame is not NULL
63 begin
64
65 if not exists (select *
66 from master.dbo.syslogins
67 where name like @loginame
68 and ((status & 512) != 512)) /* not LOGIN PROFILE */
69 begin
70 /* 17231, "No login with the specified name exists." */
71 raiserror 17231
72 rollback transaction
73 return (1)
74 end
75
76 /*
77 ** Print out the totals for this login, and compare against
78 ** overall totals.
79 */
80 select "Name" = name, "Since" = convert(char(11), accdate),
81 "CPU" = totcpu,
82 "Percent CPU" = convert(varchar(9), convert(numeric(14, 4),
83 round(convert(numeric(20, 4),
84 totcpu) / @cputot * 100, 5)))
85 + "%",
86 "I/O" = totio,
87 "Percent I/O" = convert(varchar(9), convert(numeric(14, 4),
88 round(convert(numeric(20, 4),
89 totio) / @iotot * 100, 5)))
90 + "%"
91 from master.dbo.syslogins where name like @loginame
92 and ((status & 512) != 512) /* not LOGIN PROFILE */
93
94 end
95 else
96 begin
97
98 /*
99 ** Print out the totals for all logins except sa, and compare against
100 ** overall totals.
101 */
102 select "Name" = name, "Since" = convert(char(11), accdate),
103 "CPU" = totcpu,
104 "Percent CPU" = convert(varchar(9), convert(numeric(14, 4),
105 round(convert(numeric(20, 4),
106 totcpu) / @cputot * 100, 5)))
107 + "%",
108 "I/O" = totio,
109 "Percent I/O" = convert(varchar(9), convert(numeric(14, 4),
110 round(convert(numeric(20, 4),
111 totio) / @iotot * 100, 5)))
112 + "%"
113 from master.dbo.syslogins
114 where ((status & 512) != 512) /* not LOGIN PROFILE */
115 end
116
117 /*
118 ** Print out overall totals.
119 */
120 print ""
121 select "Total CPU" = convert(varchar(15), @cputot), "Total I/O" = convert(varchar(15), @iotot)
122 commit transaction
123 return (0)
124
125
exec sp_procxmode 'sp_reportstats', 'AnyMode'
go
Grant Execute on sp_reportstats to public
go
MEST 4 Empty String will be replaced by Single Space 
MGTP 3 Grant to public master..syslogins
MRST 2 Result Set Marker 
