当前位置: 澳门新濠3559 > 数据库 > 正文

--索引使用空间,代码如下

时间:2019-11-22 23:13来源:数据库
运作下边存款和储蓄进度 然后直接运用SpaceUsed就足以查看了. 存储进程代码程序代码 复制代码代码如下:CreateprocedureSpaceUsed as begindeclare@idint--西奥bjectidof@objname.declare@typecharacter--西奥bj

运作下边存款和储蓄进度 然后直接运用SpaceUsed就足以查看了. 存储进程代码 程序代码 复制代码 代码如下:CreateprocedureSpaceUsed as begin declare@idint--西奥bjectidof@objname. declare@typecharacter--西奥bjecttype. declare@pagesint--Workingvariableforsizecalc. declare@dbnamesysname declare@dbsizedec declare@logsizedec declare@bytesperpagedec declare@pagesperMBdec declare@objnamenvarchar--西奥bjectwewantsizeon. declare@updateusagevarchar--Param.forspecifyingthat createtable#temp1 null, 行数charnull, 保留空间varcharnull, 数据采纳空间varcharnull, 索引使用空间varcharnull, 未用空间varchar --select@objname='N_dep'--usageinfo.shouldbeupdated. select@updateusage='false' /*CreatetemptablesbeforeanyDMLtoensuredynamic **Weneedtocreateatemptabletodothecalculation. **reserved:sumwhereindidin **data:sumwhereindid<2+sumwhereindid=255 **indexp:sumwhereindidin-data **unused:sumwhereindidin */ declarecur_tablecursorfor selectnamefromsysobjectswheretype='u' Opencur_table fetchnextfromcur_tableinto@objname While@@FETCH_STATUS=0 begin createtable#spt_space ( rowsintnull, reserveddecnull, indexpdecnull, unuseddec /* **Checktoseeifuserwantsusagesupdated. */ if@updateusageisnotnull begin select@updateusage=lower if@updateusagenotin begin raiserror(15143,-1,-1,@updateusage) return end end /* **Checktoseethattheobjnameislocal. */ if@objnameISNOTNULL begin select@dbname=parsename if@dbnameisnotnulland@dbname<>db_name() begin raiserror return end if@dbnameisnull select@dbname=db_name() /* **Trytofindtheobject. */ select@id=null select@id=id,@type=xtype fromsysobjects whereid=object_id /* **Doestheobjectexist? */ if@idisnull begin raiserror(15009,-1,-1,@objname,@dbname) return end ifnotexists(select*fromsysindexes where@id=idandindid<2) if@typein('P','D','R','TR','C','RF')--datastoredinsysprocedures begin raiserror return end elseif@type='V'--View=>nophysicaldatastorage. begin raiserror return end elseif@typein--nophysicaldatastorage.--?!?!toomanysimilarmessages begin raiserror return end elseif@type='F'--FK=>nophysicaldatastorage. begin raiserror return end end /* **Updateusagesifuserspecifiedtodoso. */ if@updateusage='true' begin if@objnameisnull dbccupdateusagewithno_infomsgs else dbccupdateusagewithno_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/ */ if@idisnull begin select@dbsize=sum) fromdbo.sysfiles where select@logsize=sum) fromdbo.sysfiles where select@bytesperpage=low frommaster.dbo.spt_values wherenumber=1 andtype='E' select@pagesperMB=1048576/@bytesperpage selectdatabase_name=db_name(), database_size= ltrim(str/@pagesperMB,15,2)+'MB'), 'unallocatedspace'= ltrim(str((@dbsize- (selectsum,reserved)) fromsysindexes whereindidin/@pagesperMB,15,2)+'MB') print'' /* **Nowcalculatethesummarydata. **reserved:sumwhereindidin */ insertinto#spt_space selectsum,reserved)) fromsysindexes whereindidin /* **data:sumwhereindid<2 **+sumwhereindid=255 */ select@pages=sum,dpages)) fromsysindexes whereindid<2 select@pages=@pages+isnull,used)),0) fromsysindexes whereindid=255 update#spt_space setdata=@pages /*index:sumwhereindidin-data*/ update#spt_space setindexp=(selectsum) fromsysindexes whereindidin -data /*unused:sumwhereindidin*/ update#spt_space setunused=reserved -(selectsum) fromsysindexes whereindidin 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:sumwhereindidin */ insertinto#spt_space selectsum fromsysindexes whereindidin andid=@id /* **data:sumwhereindid<2 **+sumwhereindid=255 */ select@pages=sum fromsysindexes whereindid<2 andid=@id select@pages=@pages+isnull fromsysindexes whereindid=255 andid=@id update#spt_space setdata=@pages /*index:sumwhereindidin-data*/ update#spt_space setindexp= fromsysindexes whereindidin -data /*unused:sumwhereindidin*/ update#spt_space setunused=reserved - fromsysindexes whereindidin update#spt_space setrows=i.rows fromsysindexesi wherei.indid<2 andi.id=@id insertinto#temp1 selectname=object_name, rows=convert, 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#temp1orderbylendesc,数据选拔空间desc,保留空间desc Droptable#temp1 return end GO

create procedure spaceused

declare @id            int
declare @type          character(2)                
declare @pages         int                       
declare @dbname        sysname
declare @dbsize        dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB    dec(15,0)

复制代码 代码如下:declare @id intdeclare @type character declare @pages int declare @dbname sysnamedeclare @dbsize decdeclare @bytesperpage decdeclare @pagesperMB dec

as

create table #spt_space
(
    [objid]         int null,
    [rows]          int null,
    [reserved]      dec(15) null,
    [data]          dec(15) null,
    [indexp]        dec(15) null,
    [unused]        dec(15) null
)

create table #spt_space( [objid] int null, [rows] int null, [reserved] dec null, [data] dec null, [indexp] dec null, [unused] dec

begin

set nocount on

set nocount on

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

-- Create a cursor to loop through the user   tables

-- Create a cursor to loop through the user tables

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

declare c_tables cursor for
select id from sysobjects where xtype = 'U'

declare c_tables cursor forselect id from sysobjects where xtype = 'U'

declare @pages int -- Working variable for size calc.

open c_--索引使用空间,代码如下。tables fetch next from c_tables into @id

open c_tables fetch next from c_tables into @id

declare @dbname sysname

while @@fetch_status = 0
begin
    /* Code from sp_spaceused */
    insert into #spt_space (objid, reserved)
    select objid = @id, sum(reserved)
    from sysindexes
    where indid in (0, 1, 255)   and   id = @id
   
   
    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
    where objid = @id
   
    /* 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
    where objid = @id
   
    /* 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
                            )
    where objid = @id
   
    update #spt_space   set [rows] = i.[rows]
    from sysindexes i
    where i.indid < 2   and i.id = @id    and objid = @id
   
    fetch next from c_tables   into @id

while @@fetch_status = 0begin /* Code from sp_spaceused */ insert into #spt_space select objid = @id, sum from sysindexes where indid in and id = @id select @pages = sum from sysindexes where indid < 2 and id = @id select @pages = @pages + isnull from sysindexes where indid = 255 and id = @id update #spt_space set data = @pages where objid = @id /* index: sum where indid in - data */ update #spt_space set indexp = from sysindexes where indid in and id = @id) - data where objid = @id /* unused: sum where indid in */ update #spt_space set unused = reserved - from sysindexes where indid in and id = @id ) where objid = @id update #spt_space set [rows] = i.[rows] from sysindexes i where i.indid < 2 and i.id = @id and objid = @id fetch next from c_tables into @idend

declare @dbsize dec(15,0)

end

select TableName = from sysobjects where id = objid), [Rows] = convert, ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'), DataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'), IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'), UnusedKB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')from #spt_space, master.dbo.spt_values dwhere d.number = 1and d.type = 'E'

declare @logsize dec(15)

select TableName = (select left(name,60) from sysobjects where id = objid),
        [Rows] = convert(char(11), rows),
        ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
        DataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
        IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
        UnusedKB = 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'
order by reserved desc

order by reserved desc

declare @bytesperpage dec(15,0)

drop table #spt_space
close c_tables
deallocate c_tables

drop table #spt_spaceclose c_tablesdeallocate c_tables

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(15) 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

编辑:数据库 本文来源:--索引使用空间,代码如下

关键词: