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

发布时间：2023-08-10 14:30:08 所属栏目：MsSql教程来源：

导读：运行下面存储过程然后直接使用SpaceUsed就可以查看了. 存储过程代码程序代码 <div class="codetitle"><a style="CURSOR: pointer" data="85934" class="copybut" id="copybut85934" onclick="doCopy('code8593

运行下面存储过程然后直接使用SpaceUsed就可以查看了. 存储过程代码程序代码 <div class="codetitle"><a style="CURSOR: pointer" data="85934" class="copybut" id="copybut85934" onclick="doCopy('code85934')"> 代码如下:

<div class="codebody" id="code85934">

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,255)-data unused:sum(reserved)-sum(used)whereindidin(0,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,@updateusage) return(1) end end / Checktoseethattheobjnameislocal. / if@objnameISNOTNULL begin select@dbname=parsename(@objname,3) if@dbnameisnotnulland@dbname<>db_name() begin raiserror(15250,-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,@objname,@dbname) return(1) end ifnotexists(selectfromsysindexes where@id=idandindid<2) if@typein('P','D','R','TR','C','RF')--datastoredinsysprocedures begin raiserror(15234,-1) return(1) end elseif@type='V'--View=>nophysicaldatastorage. begin raiserror(15235,-1) return(1) end elseif@typein('PK','UQ')--nophysicaldatastorage.--?!?!toomanysimilarmessages begin raiserror(15064,-1) return(1) end elseif@type='F'--FK=>nophysicaldatastorage. begin raiserror(15275,-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=@dbsized.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,255) ))/@pagesperMB,2)+'MB') print'' / Nowcalculatethesummarydata. reserved:sum(reserved)whereindidin(0,255) / insertinto#spt_space(reserved) selectsum(convert(dec(15),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,255)-data/ update#spt_space setindexp=(selectsum(convert(dec(15),used)) fromsysindexes whereindidin(0,255)) -data /unused:sum(reserved)-sum(used)whereindidin(0,255)/ update#spt_space setunused=reserved -(selectsum(convert(dec(15),255)) selectreserved=ltrim(str(reservedd.low/1024.,0)+ ''+'KB'),data=ltrim(str(datad.low/1024.,index_size=ltrim(str(indexPd.low/1024.,unused=ltrim(str(unusedd.low/1024.,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,255) / insertinto#spt_space(reserved) selectsum(reserved) fromsysindexes whereindidin(0,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,255)-data/ update#spt_space setindexp=(selectsum(used) fromsysindexes whereindidin(0,255) andid=@id) -data /unused:sum(reserved)-sum(used)whereindidin(0,255)/ update#spt_space setunused=reserved -(selectsum(used) fromsysindexes whereindidin(0,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(reservedd.low/1024.,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

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!