아래 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