Skip to content

SAP IQ: Query Tables Information

July 22, 2014

Here is a script that I use to query information about the size, creation and last update 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

Advertisements

From → SAP IQ

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: