一個(gè)查看MSSQLServer數(shù)據(jù)庫(kù)空間使用情況的存儲(chǔ)過(guò)程 SpaceUsed
發(fā)布日期：2022-02-04 12:24 | 文章來(lái)源：源碼中國(guó)
然后直接使用SpaceUsed就可以查看了.
存儲(chǔ)過(guò)程代碼
程序代碼
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, 行數(shù)char(11)null, 保留空間varchar(15)null, 數(shù)據(jù)使用空間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(數(shù)據(jù)使用空間)desc,數(shù)據(jù)使用空間desc,保留空間desc Droptable#temp1 return(0) end GO
復(fù)制代碼 代碼如下:
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, 行數(shù)char(11)null, 保留空間varchar(15)null, 數(shù)據(jù)使用空間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(數(shù)據(jù)使用空間)desc,數(shù)據(jù)使用空間desc,保留空間desc Droptable#temp1 return(0) end GO
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信