在對後台數據庫進行研究的過程中，我需要得到數據庫的某些相關信息，比如，我希望知道各個用戶表占用多少磁盤空間，並且排列出來，可以讓我知道哪些表比較大，數據比較多等等——我相信，這可能也是不少數據庫管理員所關心的問題，所以我決心做一個通用的存儲過程。我對系統的存儲過程sp_spaceused加了一些改動，以適合我的要求。希望這個存儲過程能對大家有些幫助。存儲過程如下：
if exists(select name from sysobjects where name='spaceused' and type='p')
Drop procedure spaceused
GO
create procedure spaceused
as
begin
declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @objname nvarchar(776) -- The object we want size on.
declare @updateusage varchar(5) -- Param. for specifying that
create table #temp1
(
表名 varchar(200) null,
行數 char(11) null,
保留空間 varchar(15) null,
數據使用空間 varchar(15) null,
索引使用空間 varchar(15) null,
未用空間 varchar(15) null
)
--select @objname='N_dep' -- usage info. should be updated.
select @updateusage='false'
/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
declare cur_table cursor for
您正在看的SQLserver教程是:實用的存儲過程之一。 select name from sysobjects where type='u'
Open cur_table
fetch next from cur_table into @objname
While @@FETCH_STATUS=0
begin
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
/*
** Check to see if user wants usages updated.
*/
if @updateusage is not null
begin
select @upd
您正在看的SQLserver教程是:實用的存儲過程之一。ateusage=lower(@updateusage)
if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin
select @dbname = parsename(@objname, 3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
您正在看的SQLserver教程是:實用的存儲過程之一。 end
if @dbname is null
select @dbname = db_name()
/*
** Try to find the object.
*/
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@objname)
/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
您正在看的SQLserver教程是:實用的存儲過程之一。 end
if not exists (select * from sysindexes
where @id = id and indid < 2)if @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
begin
raiserror(15234,-1,-1)
return (1)
end
else if @type = 'V ' -- VIEw => no physical data storage.
begin
raiserror(15235,-1,-1)
return (1)
您正在看的SQLserver教程是:實用的存儲過程之一。 end
else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
begin
raiserror(15064,-1,-1)
return (1)
end
else if @type = 'F ' -- FK => no physical data storage.
begin
raiserror(15275,-1,-1)
return (1)
end
end
/*
** Update usages if user specifIEd to do so.
您正在看的SQLserver教程是:實用的存儲過程之一。*/
if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end
set nocount on
/*
** If @id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @dbsize = Pages used
** @bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = 'E' and
** d.number = 1.
您正在看的SQLserver教程是:實用的存儲過程之一。** Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
if @id is null
begin
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select database_name = db_name(),
database_size =
您正在看的SQLserver教程是:實用的存儲過程之一。 ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2)+ ' MB')
print ' '
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
您正在看的SQLserver教程是:實用的存儲過程之一。 where indid in (0, 1, 255)
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
&nbs
