SYBASE에서 TABLE SIZE를 구하는 법을 알고싶습니다.
SP_IQTABLESIZE 로 구하면 1건만 SELECT되서 보여지는데
USER당 TABLE당으로 TABLESIZE를 확인하는 방법이 있을 것 같은데...
IQ를 처음쓰는 관계로 모르겠네요.
고수님들의 답변부탁드립니다.
좋은하루되세요.
아래 stored procedure를 만들어서 특정한 USER명을 입력하거나 아니면 파라미터 없이 그냥 사용하면 됩니다.
예1) sp_iqtablesizebyOwner 'DBA' ;
예2) sp_iqtablesizebyOwner;
create procedure dbo.sp_iqtablesizebyOwner(in v_OwnerName char(128) DEFAULT NULL)result(Ownername varchar(128),Tablename varchar(128),Columns char(20),KBytes char(20),Pages char(20),CompressedPages char(20),NBlocks char(20))begin declare v_TableName char(128); declare v_UserName char(128); declare local temporary table iq_tablesize_temp( Ownername varchar(128) null, Tablename varchar(128) null, Columns char(20) null, KBytes char(20) null, Pages char(20) null, CompressedPages char(20) null, NBlocks char(20) null, ) in SYSTEM on commit preserve rows; IF v_OwnerName is NULL THEN FOR curfor1 AS get_tbl1 CURSOR FOR select table_name, user_name from systable, sysuserperm where table_type='BASE' and systable.server_type='IQ' and sysuserperm.user_id = systable.creator and ( table_name not like 'sys%' and table_name not like 'rs_%' and table_name not like 'jdbc_%' and table_name not like 'EXCLUDE%' and table_name not like 'RowGen%' and table_name not like 'DUMMY%' and table_name not like 'java_%' and table_name not like 'ul_%' and table_name not like 'spt_%' ) order by creator, table_name FOR READ ONLY DO SET v_TableName = table_name; SET v_UserName = user_name; execute immediate 'iq utilities main into iq_tablesize_temp table size ' || v_UserName || '.' || v_TableName; END FOR; ELSE FOR curfor2 AS get_tbl2 CURSOR FOR select table_name from systable, sysuserperm where table_type='BASE' and systable.server_type='IQ' and sysuserperm.user_id = systable.creator and sysuserperm.user_id = user_id(v_OwnerName) and ( table_name not like 'sys%' and table_name not like 'rs_%' and table_name not like 'jdbc_%' and table_name not like 'EXCLUDE%' and table_name not like 'RowGen%' and table_name not like 'DUMMY%' and table_name not like 'java_%' and table_name not like 'ul_%' and table_name not like 'spt_%' ) order by table_name FOR READ ONLY DO SET v_TableName = table_name; execute immediate 'iq utilities main into iq_tablesize_temp table size ' || v_TableName; END FOR; END IF; select Ownername,Tablename,cast(sum(Columns) as bigint) "Col#",cast(sum(KBytes) as bigint) "KB", cast(sum(Pages) as bigint) "Page#",cast(sum(CompressedPages) as bigint) "CompressedPage#",cast(sum(NBlocks) as bigint) "Block#" from iq_tablesize_temp group by rollup (Ownername,Tablename) order by Ownername,Tablename;
drop table iq_tablesize_temp;end