bsp; @slog_dpgs numeric(20,9), /* number of data pages in syslogs */
@slog_unused numeric(20,9) /* number of unused pages in syslogs */
/* Show the database name and size */
select distinct database_name = db_name(), database_size =
ltrim(str(sum(size) / (1048576 / d.low), 10 ,1)) + " MB"
from master.dbo.sysusages, master.dbo.spt_values d
where dbid = db_id()
and d.number = 1
and d.type = "E"
having dbid = db_id()
and d.number = 1
and d.type = "E"
/*
** Obtain the page count for syslogs table.
*/
select @slog_res_pgs = convert(numeric(20,9),reserved_pgs(id, doampg)),
@slog_dpgs = convert(numeric(20,9),data_pgs(id, doampg)),
@slog_unused = convert(numeric(20,9),((reserved_pgs(id, doampg)+
reserved_pgs(id, ioampg)) -
(data_pgs(id, doampg) + data_pgs(id, ioampg))))
from sysindexes where id = 8
/*
** Obtain the page count for all the objects in the current
** database; except for 'syslogs' (id = 8). Store the results
** in a temp. table (#pgcounts).
*/
select distinct
sysindexes.name,
res_pgs = (reserved_pgs(id, doampg) + reserved_pgs(id,ioampg)),
low = d.low,
dpgs = convert(numeric(20,9),data_pgs(id, doampg)),
ipgs = convert(numeric(20,9),data_pgs(id, ioampg)),
unused = convert(numeric(20,9),((reserved_pgs(id, doampg) +
reserved_pgs(id, ioampg)) -
(data_pgs(id, doampg) + data_pgs(id, ioampg))))
into #pgcounts
from sysindexes, master.dbo.spt_values d
where sysindexes.id != 8
and d.number = 1
and d.type = "E"
having d.number = 1
and d.type = "E"
/*
** Compute the summary results by adding page counts from
** individual data objects. Add to the count the count of
** pages for 'syslogs'. Convert the total pages to space
** used in Kilo bytes.
*/
select distinct reserved = convert(char(15), convert(varchar(11),
convert(numeric(11,0),((sum(res_pgs) + @slog_res_pgs) /
1024) * low)) + " " + "KB"),
data = convert(char(15), convert(varchar(11),
convert(numeric(11,0),((sum(dpgs) + @slog_dpgs) /
1024) * low)) + " " + "KB"),
index_size = convert(char(15), convert(varchar(11),
convert(numeric(11,0), (sum(ipgs) / 1024) * low))
+ " " + "KB"),
unused = convert(char(15), convert(varchar(11),
convert(numeric(11,0),((sum(unused) + @slog_unused) /
1024) * low)) + " " + "KB")
from #pgcounts
/* collect the object information into a temp table */
select name, id, type
into #objlist
from sysobjects where type in ('S', 'U')
/*
** Now we want detail on all objects
*/
if (@tabname = "syslogs") /* syslogs */
begin
/*
** 17832, "Not avail."
*/
exec sp_getmessage 17832, @msg output
select @length = max(datalength(o.name))
from sysobjects o, sysindexes i, #objlist
where i.id = #objlist.id
and o.id = #objlist.id
if (@length > 20)
select name = o.name,
rowtotal = convert(char(11), @msg),
reserved = convert(char(10), convert(varchar(11),
convert(numeric(11,0), convert(numeric(20,9),
(reserved_pgs(i.id, i.doampg) / 1024)) *
&nbs上一页 [1] [2] [3] [4] [5] [6] 下一页 |
