Skip to Content

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.

4 Comments

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

  1. Mandeep Dhillon

    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

    (0) 
    1. Marcus Vinicius melo araujo

      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

      (0) 

Leave a Reply