这篇文章主要介绍“sybase数据库怎么找出表大小脚本”，在日常操作中，相信很多人在sybase数据库怎么找出表大小脚本问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”sybase数据库怎么找出表大小脚本”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
以下SQL脚本用于找出表使用的空间大小，结果如图
脚本如下：
use dbname
go
set nocount on
go
declare tab_name cursor
for select name
from sysobjects
where type="U"
go
open tab_name
go
begin
declare @objname sysname
declare @empty_dpgs int
create table #fmtpgcounts (
name char(35)
,rowtotal int
,reserved char(15)
,data char(15)
,index_size char(15)
,unused char(15)
)
fetch next from tab_name into @objname
while @@fetch_status = 0
begin
--print @objname
--exec sp_spaceused @objname
select name = o.name,
tabid = i.id,
iname = i.name,
indid = i.indid,
low = d.low,
rowtotal = convert(numeric(10,0), 0),
reserved = convert(numeric(20, 9), 0),
data = convert(numeric(20, 9), 0),
index_size = convert(numeric(20, 9), 0),
unused = convert(numeric(20, 9), 0)
into #pagecounts
from sysobjects o, sysindexes i, master.dbo.spt_values d
where i.id = object_id(@objname)
and o.id = i.id
and d.number = 1
and d.type = "E"
update #pagecounts
set rowtotal = row_count(db_id(), tabid)
where indid <= 1
update #pagecounts set
reserved = convert(numeric(20, 9),
reserved_pages(db_id(), tabid, indid)),
index_size = convert(numeric(20, 9),
data_pages(db_id(), tabid, indid)),
unused = convert(numeric(20, 9),
((reserved_pages(db_id(), tabid, indid) -
(data_pages(db_id(), tabid, indid)))))
where indid > 1
update #pagecounts set
reserved = convert(numeric(20, 9),
reserved_pages(db_id(), tabid, indid)),
data = convert(numeric(20, 9),
data_pages(db_id(), tabid, indid)),
unused = convert(numeric(20, 9),
((reserved_pages(db_id(), tabid, indid) -
(data_pages(db_id(), tabid, indid)))))
where indid = 0
update #pagecounts set
reserved = convert(numeric(20, 9),
reserved_pages(db_id(), tabid, 0))
+ convert(numeric(20, 9),
reserved_pages(db_id(), tabid, indid)),
index_size = convert(numeric(20, 9),
data_pages(db_id(), tabid, indid)),
data = convert(numeric(20, 9),
data_pages(db_id(), tabid, 0))
where indid = 1
update #pagecounts set
unused = convert(numeric(20, 9),
reserved - data - index_size)
where indid = 1
if(exists(select convert(char(30),a.char_value)
from sysattributes t, master.dbo.sysattributes c,
master.dbo.sysattributes a
where t.object_type = "T"
and t.object = object_id(@objname)
and c.class = 0 and c.attribute = 0
and a.class = 0 and a.attribute = 1
and t.class = c.object
and t.class = a.object
and t.attribute = a.object_info1
and a.char_value = 'hash key factors'))
begin
select @empty_dpgs = emptypgcnt
from systabstats where id = object_id(@objname)
end
else
begin
select @empty_dpgs = 0
end
insert into #fmtpgcounts
select distinct name,
rowtotal = convert(int, sum(rowtotal)),
reserved = convert(char(15), convert(varchar(11),
convert(numeric(11, 0), sum(reserved) *
(low / 1024) / 1024)) + " " + "MB"),
data = convert(char(15), convert(varchar(11),
convert(numeric(11, 0), (sum(data) - @empty_dpgs) *
(low / 1024) / 1024)) + " " + "MB"),
index_size = convert(char(15), convert(varchar(11),
convert(numeric(11, 0), sum(index_size) *
(low / 1024) / 1024)) + " " + "MB"),
unused = convert(char(15), convert(varchar(11),
convert(numeric(11, 0), (sum(unused) + @empty_dpgs) *
(low / 1024) / 1024)) + " " + "MB")
from #pagecounts
drop table #pagecounts
fetch next from tab_name into @objname
end
select distinct
'TableName' = convert(char(35),name) ,
'RowTotal' = rowtotal ,
'Reserved' = convert(char(10), reserved),
'Data' = convert(char(10), data),
'IndexSize' = convert(char(10), index_size),
'Unused' = convert(char(10), unused)
from #fmtpgcounts
-- 去掉行数为0的行
where rowtotal <> 0
order by rowtotal desc
--exec sp_autoformat #fmtpgcounts
drop table #fmtpgcounts
end
go
close tab_name
go
deallocate tab_name
go到此，关于“sybase数据库怎么找出表大小脚本”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
