一个查看MSSQLServer数据库空间使用情况的存储过程 SpaceUsed

运行下面存储过程

然后直接使用SpaceUsed就可以查看了.

存储过程代码

程序代码

复制代码 代码如下:
CreateprocedureSpaceUsed

as

begin

declare@idint--Theobjectidof@objname.

declare@typecharacter(2)--Theobjecttype.

declare@pagesint--Workingvariableforsizecalc.

declare@dbnamesysname

declare@dbsizedec(15,0)

declare@logsizedec(15)

declare@bytesperpagedec(15,0)

declare@pagesperMBdec(15,0)

declare@objnamenvarchar(776)--Theobjectwewantsizeon.

declare@updateusagevarchar(5)--Param.forspecifyingthat

createtable#temp1

(

表名varchar(200)null,

行数char(11)null,

保留空间varchar(15)null,

数据使用空间varchar(15)null,

索引使用空间varchar(15)null,

未用空间varchar(15)null

)

--select@objname='N_dep'--usageinfo.shouldbeupdated.

select@updateusage='false'

/*CreatetemptablesbeforeanyDMLtoensuredynamic

**Weneedtocreateatemptabletodothecalculation.

**reserved:sum(reserved)whereindidin(0,1,255)

**data:sum(dpages)whereindid<2+sum(used)whereindid=255(text)

**indexp:sum(used)whereindidin(0,1,255)-data

**unused:sum(reserved)-sum(used)whereindidin(0,1,255)

*/

declarecur_tablecursorfor

selectnamefromsysobjectswheretype='u'

Opencur_table

fetchnextfromcur_tableinto@objname

While@@FETCH_STATUS=0

begin

createtable#spt_space

(

rowsintnull,

reserveddec(15)null,

datadec(15)null,

indexpdec(15)null,

unuseddec(15)null

)

/*

**Checktoseeifuserwantsusagesupdated.

*/

if@updateusageisnotnull

begin

select@updateusage=lower(@updateusage)

if@updateusagenotin('true','false')

begin

raiserror(15143,-1,-1,@updateusage)

return(1)

end

end

/*

**Checktoseethattheobjnameislocal.

*/

if@objnameISNOTNULL

begin

select@dbname=parsename(@objname,3)

if@dbnameisnotnulland@dbname<>db_name()

begin

raiserror(15250,-1,-1)

return(1)

end

if@dbnameisnull

select@dbname=db_name()

/*

**Trytofindtheobject.

*/

select@id=null

select@id=id,@type=xtype

fromsysobjects

whereid=object_id(@objname)

/*

**Doestheobjectexist?

*/

if@idisnull

begin

raiserror(15009,-1,-1,@objname,@dbname)

return(1)

end

ifnotexists(select*fromsysindexes

where@id=idandindid<2)

if@typein('P','D','R','TR','C','RF')--datastoredinsysprocedures

begin

raiserror(15234,-1,-1)

return(1)

end

elseif@type='V'--View=>nophysicaldatastorage.

begin

raiserror(15235,-1,-1)

return(1)

end

elseif@typein('PK','UQ')--nophysicaldatastorage.--?!?!toomanysimilarmessages

begin

raiserror(15064,-1,-1)

return(1)

end

elseif@type='F'--FK=>nophysicaldatastorage.

begin

raiserror(15275,-1,-1)

return(1)

end

end

/*

**Updateusagesifuserspecifiedtodoso.

*/

if@updateusage='true'

begin

if@objnameisnull

dbccupdateusage(0)withno_infomsgs

else

dbccupdateusage(0,@objname)withno_infomsgs

print''

end

setnocounton

/*

**If@idisnull,thenwewantsummarydata.

*/

/*Spaceusedcalculatedinthefollowingway

**@dbsize=Pagesused

**@bytesperpage=d.low(whered=master.dbo.spt_values)is

**the#ofbytesperpagewhend.type='E'and

**d.number=1.

**Size=@dbsize*d.low/(1048576(OR1MB))

*/

if@idisnull

begin

select@dbsize=sum(convert(dec(15),size))

fromdbo.sysfiles

where(status&64=0)

select@logsize=sum(convert(dec(15),size))

fromdbo.sysfiles

where(status&64<>0)

select@bytesperpage=low

frommaster.dbo.spt_values

wherenumber=1

andtype='E'

select@pagesperMB=1048576/@bytesperpage

selectdatabase_name=db_name(),

database_size=

ltrim(str((@dbsize+@logsize)/@pagesperMB,15,2)+'MB'),

'unallocatedspace'=

ltrim(str((@dbsize-

(selectsum(convert(dec(15),reserved))

fromsysindexes

whereindidin(0,1,255)

))/@pagesperMB,15,2)+'MB')

print''

/*

**Nowcalculatethesummarydata.

**reserved:sum(reserved)whereindidin(0,1,255)

*/

insertinto#spt_space(reserved)

selectsum(convert(dec(15),reserved))

fromsysindexes

whereindidin(0,1,255)

/*

**data:sum(dpages)whereindid<2

**+sum(used)whereindid=255(text)

*/

select@pages=sum(convert(dec(15),dpages))

fromsysindexes

whereindid<2

select@pages=@pages+isnull(sum(convert(dec(15),used)),0)

fromsysindexes

whereindid=255

update#spt_space

setdata=@pages

/*index:sum(used)whereindidin(0,1,255)-data*/

update#spt_space

setindexp=(selectsum(convert(dec(15),used))

fromsysindexes

whereindidin(0,1,255))

-data

/*unused:sum(reserved)-sum(used)whereindidin(0,1,255)*/

update#spt_space

setunused=reserved

-(selectsum(convert(dec(15),used))

fromsysindexes

whereindidin(0,1,255))

selectreserved=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_valuesd

whered.number=1

andd.type='E'

end

/*

**Wewantaparticularobject.

*/

else

begin

/*

**Nowcalculatethesummarydata.

**reserved:sum(reserved)whereindidin(0,1,255)

*/

insertinto#spt_space(reserved)

selectsum(reserved)

fromsysindexes

whereindidin(0,1,255)

andid=@id

/*

**data:sum(dpages)whereindid<2

**+sum(used)whereindid=255(text)

*/

select@pages=sum(dpages)

fromsysindexes

whereindid<2

andid=@id

select@pages=@pages+isnull(sum(used),0)

fromsysindexes

whereindid=255

andid=@id

update#spt_space

setdata=@pages

/*index:sum(used)whereindidin(0,1,255)-data*/

update#spt_space

setindexp=(selectsum(used)

fromsysindexes

whereindidin(0,1,255)

andid=@id)

-data

/*unused:sum(reserved)-sum(used)whereindidin(0,1,255)*/

update#spt_space

setunused=reserved

-(selectsum(used)

fromsysindexes

whereindidin(0,1,255)

andid=@id)

update#spt_space

setrows=i.rows

fromsysindexesi

wherei.indid<2

andi.id=@id

insertinto#temp1

selectname=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_valuesd

whered.number=1

andd.type='E'

Droptable#spt_space

end

fetchnextfromcur_tableinto@objname

end

Closecur_table

DEALLOCATEcur_table

Select*from#temp1orderbylen(数据使用空间)desc,数据使用空间desc,保留空间desc

Droptable#temp1

return(0)

end

GO

(责任编辑：admin)