实用的存储过程之一
笔者工作的公司采用的是sqlserver数据库，每天都要处理大量的数据，由于笔者进公司的时间比较晚，公司现有的大部分的程序都是以前的程序员留下的，因为他们没有相关的文档，笔者对于后台数据库的很多表的结构和数据都不甚了解，给日常的维护造成了很大的麻烦。
在对后台数据库进行研究的过程中，我需要得到数据库的某些相关信息，比如，我希望知道各个用户表占用多少磁盘空间，并且排列出来，可以让我知道哪些表比较大，数据比较多等等——我相信，这可能也是不少数据库管理员所关心的问题，所以我决心做一个通用的存储过程。我对系统的存储过程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
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 @updateusage=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)
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)
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)
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.
*/
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.
** 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 =
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
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
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'kb'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'kb'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'kb'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'kb')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'e'
end
/*
** we want a particular object.
*/
else
begin
/*
** now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
insert into #temp1
select name = object_name(@id),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'kb'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'kb'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'kb'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'kb')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'e'
drop table #spt_space
end
fetch next from cur_table into @objname
end
close cur_table
deallocate cur_table
select * from #temp1 order by len(保留空间) desc,保留空间 desc
drop table #temp1
return (0)
end
原理很简单，相信大家都能看懂，sp_spaceused几乎原封不动地保留下来，调用也很简单，直接执行即可，没有任何参数，存储过程执行后，将把当前连接的数据库中所有数据表按照从大到小排列出来，还有其他的相关信息。如果能对大家有所参考价值，就请大家能给forgot2000一点掌声鼓励吧，谢谢！
本存储过程在sqlserver7.0/2000下通过。
本人qq:33563255,e-mail:[email protected]
