Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

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

4 Comments
Labels in this area