How to get the record count for all the table in sybase IQ

[Former Member]

We need to see what are the tables having huge volume and needs to a backup and clean the existing data.

Since there are veryu huge no of tables we need to get the record counts for all the tables with a single query .

Can any one of them created any stored proc or query to get this ouptu if so please help me also to close this actviity .

Out put table looks like

TName   !!Current Table size!! Prev_table_size !!current Record count !!prev record count !!column count !!  Index count1!!current  Index size!!Prev Index size!!

Please suggest and help at the earliest

Thanks In advance

Yedu

[tom_kim · Accepted solution]

try this proc:

CREATE PROCEDURE getRowCounts()
result ("table_name" char(128), rowcnt  bigint)
on exception resume
begin
  declare v_tableName varchar(128);
  declare sqlcmd varchar(256);
  declare v_rowcnt unsigned bigint;
  declare state integer;
  declare tableName dynamic scroll cursor for
      select table_name from SYSTABLE  where table_type='BASE' and server_type='IQ' and creator > 0
         for read only;
  declare local temporary table
   rowCounts(
    tblName char(128),
    rowcnt  bigint);

  select table_name as "tblName", cast(0 as bigint) as "rowcnt"  into rowCounts from
   SYSTABLE where table_type='BASE' and server_type='IQ' and creator > 0;

  open tableName;
  rowcount_loop:
  loop
    fetch next tableName into v_tableName;
    set state = sqlstate;
    if state <> '00000' then
      close tableName;
      leave rowcount_loop;
    end if;
  --set sqlcmd = 'select count(*) into v_rowcnt from ' + v_tableName;
  set sqlcmd = 'update rowCounts set rowcnt=(select count(*) from '+v_tableName + ') where rowCounts.tblName = '''+v_tableName + '''';
  execute immediate sqlcmd;
  end loop rowcount_loop;
  select * from rowCounts;
end;

[Former Member]

Awesome !         Thanks Tom KIm.Its working .

However I have some few doubts on How to call this procedure in another procedure when I am trying to call this Procedure in another procedure its not working so I remove the Paramters in the create procedure compiled then its working .

Thanks Once again .