Here is a script that I use to query information about the size, creation and last update date of tables in SAP IQ.

I’d tested this script in SAP IQ 12.6, 15.2 and 16.0 SP8.

/*

Script        : sr_TablesInfo

Version       : 1.3

Description   : Display information about the tables on the database

Creation date : 2010-12-16

Last updated  : 2014-07-22

Author        : USomarriba

*/

DECLARE

    @owner varchar(100),

    @tablename varchar(100),

    @Creation   date,

@LastUpdate date,

@rowcount   integer,

@SizeMB     numeric(18,2),

@SizePg     int,

@SizeBlocks int;

create table #Tables

(Tablename varchar (100),

Rowcount integer ,

SizeMB numeric(18,2),

Pages integer,

Blocks integer,

CreationDate datetime,

UpdateDate datetime);

declare table_cursor cursor for

select U.[user_name], T1.[table_name], T2.Create_time, T2.Update_time

      from systable T1, sysuser U, sysiqtable T2

where U.user_id = T1.creator

      and T2.table_id = T1.table_id

      and T1.table_type = ‘BASE’;

open table_cursor;

fetch table_cursor into @owner, @tablename, @Creation, @LastUpdate;

while (@@sqlstatus = 0)

begin

execute (‘select @rowcount = COUNT(*) FROM ‘ + @owner + ‘.’ + @tablename)

execute (‘select @SizeMb = (KBytes/1024) , @SizePg = Pages, @SizeBlocks = NBlocks

                from sp_iqtablesize(”’ + @owner + ‘.’ + @tablename  + ”’)’)

insert into #tables (Tablename, Rowcount, SizeMb, Pages, Blocks, CreationDate, UpdateDate)

      values (@owner + ‘.’ + @tablename, @rowcount, @SizeMb, @SizePg, @SizeBlocks, @Creation, @LastUpdate)

fetch table_cursor into @owner, @tablename, @Creation, @LastUpdate

end;

close table_cursor;

deallocate cursor table_cursor;

select * from #tables

order by Rowcount desc;

drop table #tables



This post was first published in my personal blog at SAP IQ: Query Tables Information | ARBIME

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply