存档

文章标签 ‘代理表’,文章数:2

曾经写过博文介绍利用代理表统计数据库内所有表占用空间情况,也包括表的行数:

可以参考:ASE15.0中利用代理表实现统计用户表存储空间大小的功能

配置代理表可能有些麻烦,本文提供一个简单的脚本来获得数据库内表的行数。

#!/bin/bash

ISQL="isql -Usa -P -SSYBASE -w5000"
database_name="tpchdb"

$ISQL  <<EOF  | sed -e '1,2d' -e 's/^ *//;s/ *$//' -e '/^$/d' > tablename.list
use ${database_name}
go
set nocount on
go
select name from sysobjects where type='U' order by name
go
quit
EOF

while read table_name
do
        if [ "${table_name}" = "" ]; then
                break
        fi
        $ISQL <<EOF | grep "=" | sed -e 's/^ *//;s/ *$//;s/= */=/'
use ${database_name}
go
set nocount on
go
select "${table_name}=",count(*) from ${table_name}
go
quit
EOF
done < tablename.list
echo "Table Count:`wc -l tablename.list|awk '{print $1}'`"
rm -f tablename.list

使用的时候,仅仅需要修改一下红色标记的用户名、密码、服务器名称、数据库名称即可。

当然你也可以只统计相应的表的行数,修改select name from sysobjects where type='U' order by name,比如:name like 'flow_%' and crdate > '2014-01-01'

另外,如果不想用脚本而在TSQL中实现的话,可能得用到游标了。

脚本tablerowcount.sh下载地址

 

在ASE中统计某个用户表的信息,比如:表中的行数、分配的空间、数据所占空间、索引所占空间以及剩余空间等,我们可以使用系统存储过程sp_spaceused来实现。

系统存储过程sp_spaceused有两个参数:@objname和@list_indices,第一个@objname是待统计的对象名,一般 是表名;第二个@list_indices标志是否单独统计索引信息,@list_indices=1表示单独统计索引信 息,@list_indices=0则不单独统计。

示例:

1> sp_spaceused sysobjects,0
2> go
 name       rowtotal reserved data   index_size unused
 ---------- -------- -------- ------ ---------- ------
 sysobjects 1014     252 KB   132 KB 68 KB      52 KB
(1 row affected)
(return status = 0)
1> sp_spaceused sysobjects,1
2> go
 index_name   size  reserved unused
 ------------ ----- -------- ------
 ncsysobjects 48 KB 64 KB    16 KB
 csysobjects  20 KB 32 KB    12 KB
(1 row affected)
 name       rowtotal reserved data   index_size unused
 ---------- -------- -------- ------ ---------- ------
 sysobjects 1014     252 KB   132 KB 68 KB      52 KB
(return status = 0)
1>

但是利用sp_spaceused还不能实现统计某个库里面哪张表的数据记录数最多,哪张表占用的空间最大,哪些表的行数为0,以及哪些表的索引所占空间大于100M等等的问题。

如何实现呢?

方法一:改造sp_spaceused过程语法中的SQL语句。但是,sp_spaceused过程的源代码有560多行,看起来比较累,至少需要一个临时表存储中间临时数据。


方法二:是本博客专门推荐使用的,利用ASE中的代理表来实现。下面是操作步骤:

假设用户数据库名字为:andkylee

先执行:use andkylee

            go

 

步骤一:建立代理表tablespace2

create  existing table tablespace2(name varchar(128) null,rowtotal char(15) ,reserved char(15),[data] char(15),index_size char(15),unused char(15),_objname varchar(128) null,_list_indices int null) external procedure at "loopback.andkylee.dbo.sp_spaceused"

代理表的列 有:name,rowtotal,reserved,data,index_size,unused,_objname,_list_indices,其 中name,rowtotal,reserved,data,index_size,unused这几列接收存储过程sp_spaceused返回的结果 数据。

注意name,rowtotal,reserved,data,index_size,unused的各列的数据类型要和sp_spaceused返回的列的类型一致。

_objname,_list_indices 是过程sp_spaceused的参数列。

需要再sysservers中加入一个指向自己的远程服务器loopback。

sp_addserver loopback,null,@@servername
go

如果@@servername为空,则写dsedit中显示服务器名。

 

步骤二:在代理表tablespace2上建立视图,统计库内所有用户表的存储空间

create view TableSize(name,rowtotal,reserved,data_KBytes,index_size,unused)
as
select name,cast(str_replace(rowtotal,' KB','') as int),cast(str_replace(reserved,' KB','') as int),cast(str_replace(data,' KB','') as int),cast(str_replace(index_size,' KB','') as int),cast(str_replace(unused,' KB','') as int) from tablespace2 where _list_indices  = 0
and _objname in
     (
        select user_name(uid)+"."+name from andkylee.dbo.sysobjects where type='U'
     )
go

说明:可以使用其它的条件来仅仅统计某些表,比如:只统计用户test所拥有的表的空间信息,那么改为: where type='U' and uid=user_id("test")


步骤三: 针对视图TableSize,根据要求写SQL语句

比如:统计记录数最多的10个表的表名、行数、分配空间,用如下的SQL:

select top 10 name,rowtotal,reserved  from TableSize order by rowtotal desc

go

1> select top 10 name,rowtotal,reserved  from TableSize order by rowtotal desc
2> go
 name
         rowtotal    reserved
 -------------------------------------------------------------------------------
-------------------------------------------------
         ----------- -----------
 SOS_Y_Hs_Xell
             1044911      178308
 test3
             1008576       21940
 test2
             1008576       21992
 SOS_Y_Wharehouse
              380000       31488
 SOS_Y_Hs_Xell_copy_1
              288097       49596
 FLOWREC1
              285139       40580
 PartitionTestTable
              262144       11368
 lzftest
              262144        7496
 SOS_Y_Hs_Xell_Back
              236000       40512
 SOS_R_Wharehouse
              192000       38408
(10 rows affected)

 

根据自己的要求写针对TableSize的相应的SQL语句,上面的sql在此抛砖引玉。

备注:

1、由于每次统计过程中都要涉及到待统计表的存储空间大小的计算,所以,上面介绍的方法对系统性能会有一点小的影响。只要不是频繁的执行,个人感觉影响可以忽略。

2、另外针对数据库日志也就是系统表syslogs的统计结果和其它表的不一样。

1> sp_spaceused syslogs
2> go
 name            total_pages     free_pages      used_pages
         reserved_pages
 --------------- --------------- --------------- ---------------
         ---------------
 syslogs         102400          101945          455
         0
(1 row affected)
(return status = 0)
1>

所以,不要在上面的被统计表中包含syslogs表。

————————————————————————————————-
—- 本文为andkylee个人原创,请在尊重作者劳动成果的前提下进行转载;
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字:ASE sp_spaceused 代理表 存储过程 统计表的空间大小 行数最多的表的记录数

                 所占空间最大的表
————————————————————————————————-