Former Member

July 22, 2014 1 minute read

SAP IQ: Query Tables Information

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

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

Gi-sung Jang

August 20, 2014 at 2:34 am

Hi,

It's a very useful tips for me.

Thanks

Gi-sung Jang

Former Member

Blog Post Author

August 20, 2014 at 2:05 pm

I'm glad this was useful for you. Thanks a lot for your encouraging comments.

Uvernes.

Former Member

August 1, 2017 at 10:43 pm

Hi,

I have been trying to run this, but every time I get a "Cursor has not been declared" error on the following line of code:

open table_cursor;

although it is has been declared in the previous lines.

Thank you.

Mandeep

Marcus Vinicius melo araujo

August 2, 2017 at 7:34 pm

Hi Mandeep,

just remove semicolon,

DECLARE
 @owner varchar(100),
 @tablename varchar(100),
 @Creation   date,
 @LastUpdate date,
 @rowcount   integer,
 @SizeMB     numeric(18,2),
 @SizePg     int,
 @SizeBlocks int

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  U.[user_name] = 'stg1_trf1'
      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

att

SAP IQ: Query Tables Information

Assigned Tags