一个查看MSSQLServer数据库空间使用情况的存储过程 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