sql - Stored Procedure with system tables -

i have 2 different system tables in database: systables (which stores tables of database) , syscolumns (which stores columns of tables of database).

• systables contains: tabname , tabid;
• syscolumns contains: colname, tabid, colno.

i have write following procedure:

• i have 2 new tables - tabela_sys (contains ns_tabela , nome), , campo_sys (ns_campo, ns_tabela, nome).
• i want store data of systables not exist in tabela_sys , want store data of syscolumns not exist in campo_sys.
• for that, made following procedure , tested 1 case in created new column in existing table.
• it expected after executing procedure, tabela_sys keep number of rows equal , campo_sys recognize 1 new row.

it not happening , campo_sys has many more rows, after performing procedure.

does know wrong?

create procedure test ()  define cont       integer; define cont2       integer; define tabidnum       integer; define vartabname,aa       varchar (50,0); define nstabela       integer; define strcolname       varchar (50,0); define colidnum       integer;      foreach cur1 hold          select  tabid, tabname            tabidnum,vartabname            systables           tabname not matches "sys*"          --let aa=vartabname;          select  count(*), ns_tabela         cont, nstabela         tabela_sys         nome = vartabname         group 2;          --let nstabela=0;          if cont = 0 or cont null                 insert tabela_sys (ns_tabela, nome)              values (0, vartabname);              select dbinfo('sqlca.sqlerrd1')               nstabela               systables              tabname='systables';         end if;      foreach cur2 hold          select  tabid, colname             colidnum, strcolname            syscolumns            tabid = tabidnum                 --and   colname not in (select nome campo_sys ns_tabela = nstabela)            select  count(*)         cont2         campo_sys         nome = strcolname         ,   ns_tabela = nstabela;          --let nscampo=0;          if cont2 = 0 or cont2 null               insert campo_sys (ns_tabela, nome) --(ns_campo, ns_tabela, nome)              values (colidnum, strcolname); --(0, colidnum, strcolname);          end if;      end foreach      end foreach  end procedure; 

of course number of rows different tabela_sys , campo_sys because second foreach run each columns in syscolumns , whenever new column found table campo_sys inserted.

the requirement of yours never give equal number of records unless syscolumns table 1 (means 1 column in table think not ideal situation) when using existing procedure.

ps:i not have enough reputation comment hence adding answer here. may accept answer if answered question ;)