澳门贵宾会注册送豪礼一个查看MSSQLServer数据库空间使用情况的存储过程 SpaceUsed

运维下边存款和储蓄进程 然后一向选用SpaceUsed就能够查阅了. 存款和储蓄进程代码
程序代码 复制代码
代码如下:CreateprocedureSpaceUsed as begin
declare@idint–西奥bjectidof@objname.
declare@typecharacter(2卡塔尔–西奥bjecttype.
declare@pagesint–Workingvariableforsizecalc. declare@dbnamesysname
declare@dbsizedec(15,0State of Qatar declare@logsizedec(15卡塔尔declare@bytesperpagedec(15,0State of Qatar declare@pagesperMBdec(15,0卡塔尔(قطر‎declare@objnamenvarchar(776卡塔尔(قطر‎–西奥bjectwewantsizeon.
declare@updateusagevarchar(5卡塔尔国–Param.forspecifyingthat
createtable#temp1 ( 表名varchar(200卡塔尔(قطر‎null, 行数char(11卡塔尔(قطر‎null,
保留空间varchar(15State of Qatarnull, 数据利用空间varchar(15卡塔尔国null,
索引使用空间varchar(15卡塔尔(قطر‎null, 未用空间varchar(15State of Qatarnull 卡塔尔国–select@objname=’N_dep’–usageinfo.shouldbeupdated.
select@updateusage=’false’
/*CreatetemptablesbeforeanyDMLtoensuredynamic
**Weneedtocreateatemptabletodothecalculation.
**reserved:sum(reserved)whereindidin(0,1,255)
**data:sum(dpages)whereindid2+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@dbnamedb_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=idandindid2)
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&640) 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)whereindid2
**+sum(used)whereindid=255(text) */
select@pages=sum(convert(dec(15),dpages)) fromsysindexes whereindid2
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)whereindid2
**+sum(used)whereindid=255(text) */ select@pages=sum(dpages)
fromsysindexes whereindid2 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.indid2 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

create procedure spaceused

as

begin

declare @id int — The object id of @objname.

declare @type character(2) — The object type.

declare @pages int — Working variable for size calc.

declare @dbname sysname

declare @dbsize dec(15,0)

declare @logsize dec(15)

declare @bytesperpage dec(15,0)

declare @pagesperMB dec(15,0)

declare @objname nvarchar(776) — The object we want size on.

declare @updateusage varchar(5) — Param. for specifying that

create table #temp1

(

tablename varchar(200) null,–表名

rownum char(11) null,–行数

baoliukj varchar(15卡塔尔(قطر‎ null,–保留空间

datausekj varchar(15State of Qatar null,–数据运用空间

indexkj varchar(15卡塔尔国 null,–索引使用空间

nousekj varchar(15卡塔尔国 null–未用空间

)

–select @objname=”N_dep” — usage info. should be updated.

select @updateusage=’false’

/*Create temp tables before any DML to ensure dynamic

** We need to create a temp table to do the calculation.

** reserved: sum(reserved) where indid in (0, 1, 255)

** data: sum(dpages) where indid < 2 + sum(used) where indid = 255
(text)

** indexp: sum(used) where indid in (0, 1, 255) – data

** unused: sum(reserved) – sum(used) where indid in (0, 1, 255)

*/

declare cur_table cursor for

select name from sysobjects where type=’u’

Open cur_table

fetch next from cur_table into @objname

While @@FETCH_STATUS=0

begin

create table #spt_space

(

rows int null,

reserved dec(15) null,

data dec(15) null,

indexp dec(15) null,

unused dec(15) null

)

/*

** Check to see if user wants usages updated.

*/

if @updateusage is not null

begin

select @updateusage=lower(@updateusage)

if @updateusage not in (‘true’,’false’)

begin

raiserror(15143,-1,-1,@updateusage)

return(1)

end

end

/*

** Check to see that the objname is local.

*/

if @objname IS NOT NULL

begin

select @dbname = parsename(@objname, 3)

if @dbname is not null and @dbname <> db_name()

begin

raiserror(15250,-1,-1)

return (1)

end

if @dbname is null

select @dbname = db_name()

/*

** Try to find the object.

*/

select @id = null

select @id = id, @type = xtype

from sysobjects

where id = object_id(@objname)

/*

** Does the object exist?

*/

if @id is null

begin

raiserror(15009,-1,-1,@objname,@dbname)

return (1)

end

if not exists (select * from sysindexes

where @id = id and indid < 2)

if @type in (‘P ‘,’D ‘,’R ‘,’TR’,’C ‘,’RF’) –data stored in
sysprocedures

begin

raiserror(15234,-1,-1)

return (1)

end

else if @type = ‘V ‘ — View => no physical data storage.

begin

raiserror(15235,-1,-1)

return (1)

end

else if @type in (‘PK’,’UQ’) — no physical data storage. –?!?! too
many similar messages

begin

raiserror(15064,-1,-1)

return (1)

end

else if @type = ‘F ‘ — FK => no physical data storage.

begin

raiserror(15275,-1,-1)

return (1)

end

end

/*

** Update usages if user specified to do so.

*/

if @updateusage = ‘true’

begin

if @objname is null

dbcc updateusage(0) with no_infomsgs

else

dbcc updateusage(0,@objname) with no_infomsgs

print ”

end

set nocount on

/*

** If @id is null, then we want summary data.

*/

/* Space used calculated in the following way

** @dbsize = Pages used

** @bytesperpage = d.low (where d = master.dbo.spt_values) is

** the # of bytes per page when d.type = ”E” and

** d.number = 1.

** Size = @dbsize * d.low / (1048576 (OR 1 MB))

*/

if @id is null

begin

select @dbsize = sum(convert(dec(15),size))

from dbo.sysfiles

where (status & 64 = 0)

select @logsize = sum(convert(dec(15),size))

from dbo.sysfiles

where (status & 64 <> 0)

select @bytesperpage = low

from master.dbo.spt_values

where number = 1

and type = ‘E’

select @pagesperMB = 1048576 / @bytesperpage

select database_name = db_name(),

database_size =

ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ‘MB’),

‘unallocated space’ =

ltrim(str((@dbsize –

(select sum(convert(dec(15),reserved))

from sysindexes

where indid in (0, 1, 255)

)) / @pagesperMB,15,2)+ ‘ MB’)

print ”

/*

** Now calculate the summary data.

** reserved: sum(reserved) where indid in (0, 1, 255)

*/

insert into #spt_space (reserved)

select sum(convert(dec(15),reserved))

from sysindexes

where indid in (0, 1, 255)

/*

** data: sum(dpages) where indid < 2

** + sum(used) where indid = 255 (text)

*/

select @pages = sum(convert(dec(15),dpages))

from sysindexes

where indid < 2

select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)

from sysindexes

where indid = 255

update #spt_space

set data = @pages

/* index: sum(used) where indid in (0, 1, 255) – data */

update #spt_space

set indexp = (select sum(convert(dec(15),used))

from sysindexes

where indid in (0, 1, 255))

– data

/* unused: sum(reserved) – sum(used) where indid in (0, 1, 255) */

update #spt_space

set unused = reserved

– (select sum(convert(dec(15),used))

from sysindexes

where indid in (0, 1, 255))

select 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_values d

where d.number = 1

and d.type = ‘E’

end

/*

** We want a particular object.

*/

else

begin

/*

** Now calculate the summary data.

** reserved: sum(reserved) where indid in (0, 1, 255)

*/

insert into #spt_space (reserved)

select sum(reserved)

from sysindexes

where indid in (0, 1, 255)

and id = @id

/*

** data: sum(dpages) where indid < 2

** + sum(used) where indid = 255 (text)

*/

select @pages = sum(dpages)

from sysindexes

where indid < 2

and id = @id

select @pages = @pages + isnull(sum(used), 0)

from sysindexes

where indid = 255

and id = @id

update #spt_space

set data = @pages

/* index: sum(used) where indid in (0, 1, 255) – data */

update #spt_space

set indexp = (select sum(used)

from sysindexes

where indid in (0, 1, 255)

and id = @id)

– data

/* unused: sum(reserved) – sum(used) where indid in (0, 1, 255) */

update #spt_space

set unused = reserved

– (select sum(used)

from sysindexes

where indid in (0, 1, 255)

and id = @id)

update #spt_space

set rows = i.rows

from sysindexes i

where i.indid < 2

and i.id = @id

insert into #temp1

select name = 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_values d

where d.number = 1

and d.type = ‘E’

Drop table #spt_space

end

fetch next from cur_table into @objname

end

Close cur_table

DEALLOCATE cur_table

Select * from #temp1 order by len(baoliukj) desc,baoliukj desc

Drop table #temp1

return (0)

end

GO

发表评论

电子邮件地址不会被公开。 必填项已用*标注