运行下面存储过程
然后直接使用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
【一个查看MSSQLServer数据库空间使用情况的存储过程 SpaceUsed】相关文章：