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

这篇文章主要为大家详细介绍了一个查看SQL ServerServer数据库空间使用情况的存储过程 SpaceUsed，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

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

注：关于一个查看SQL ServerServer数据库空间使用情况的存储过程 SpaceUsed的内容就先介绍到这里，更多相关文章的可以留意