分享
 
 
 

实用的存储过程之一

王朝other·作者佚名  2006-01-08
窄屏简体版  字體: |||超大  

笔者工作的公司采用的是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下通过。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有