当前位置:首页 > 服务端 > spused

spused


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

作者:华博
来源链接:https://www.cnblogs.com/RayCheung/archive/2006/02/13/329579.html

版权声明:
1、Java侠(https://www.javaxia.com)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。





本文链接:https://www.javaxia.com/server/125636.html

标签: unused variable
分享给朋友: