How to create a Stored Procedure to count the rows of each table in a database?
Gustavo Echenique wanted an SPL procedure on IDS 7.31 to count rows of every table, but got an error that the variable was not a table. Mike Walker said table names cannot be variables and suggested sysmaster:systabinfo ti_nrows. Henri Cujass and Tomas Zahradnik suggested sysmaster queries or unloading generated SQL. Art Kagel posted a sysmaster procedure using systabnames and sysptnhdr.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Dear All:
I need to create a Stored Procedure that will count the rows of each table in my database, but I can't do it.
I tried with:
DROP PROCEDURE "informix".RowCount
CREATE PROCEDURE "informix".RowCount( ) RETURNING Varchar(50,0), Integer;
DEFINE vTable VARCHAR(50);
DEFINE vRows INTEGER;
FOREACH
SELECT TabName, Tabid
INTO vTable, vTabid
FROM Systables
WHERE Tabid > 99
AND TabType = 'T'LET vRows = ( SELECT COUNT(*) FROM vTable );
RETURN vTable, vRows WITH RESUME;
END FOREACH;
END PROCEDURE;
EXECUTE PROCEDURE "informix".RowCount()
But it throws me an error saying that vTable is not a database table.
What puzzles me is that if I do the following:
select 'select count(*) from ' || trim( tabname ) || ';' from systables where tabid > 99 and tabtype = 'T' order by 1
It shows me the select count(*) with the name of each table.
It is not feasible for me to use this, since there are more than nine hundred tables.
That's why I need to do it via a Stored Procedure;
I would appreciate it if you could give me a hand.
Oh, by the way, the Informix is a 7.31 TD6
Greetings!
Gustavo
------------------------------
Gustavo Echenique
------------------------------
The problem is that you can't use a variable name as a table name in this fashion within a procedure. You could prepare a SQL statement with the table name and run that but I think that is not possible in 7.31. However, this does seem an odd thing to want to do.
Have you tried querying the systabinfo table in sysmaster to get ti_nrows?
select t.tabname, i.ti_nrows nrows
from systables t, sysmaster:systabinfo i
where t.partnum = i.ti_partnum
and t.tabtype = "T"
and t.tabid > 99This is just a quick SQL and won't work for fragmented tables in this simple form, and depends on the logging mode of your database, but this seems a bit simpler than a stored procedure.
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
------------------------------
Hi Gustavo,
similar to Mike, but with a select to sysmaster only. You can use procedure variable/paramter for the filter for example: databasename and/or tablename and/or nrows if you like to make it more flexible with a procedure.
> echo "select dbsname,tabname,ti_nrows from systabnames,systabinfo where ti_partnum=partnum order by 1,2"|dbaccess sysmaster
Regards
Henri
------------------------------
Henri Cujass
leolo IT, CTO
Germany
IBM Champion 2021 2022 2023
IIUG Board Member
------------------------------
Hi Gustavo,
you can also just wrap the query you've mentioned into an UNLOAD statement and generate the queries for all your tables:
unload to 'getrows.sql' delimiter ';'
select 'select "' || trim( tabname ) ||'", count(*) from ' || trim( tabname )
from systables
where tabid > 99 and tabtype = 'T'
order by 1;cat getrows.sql
select "tab1", count(*) from tab1;
select "tab2", count(*) from tab2;
select "tab3", count(*) from tab3;
select "tab4", count(*) from tab4;
...
------------------------------
-tz-
https://www.linkedin.com/in/tomzahr/
------------------------------
Here is a better procedure. Install it in the sysmaster database. I am making it generic so others can use it for multiple databases.
CREATE PROCEDURE table_rowcounts( dbs char(128)) returning char(128) AS tab, bigint AS cntDEFINE tbl charĺ128);
DEFINE cntr bigint;
FOREACH
SELECT tabname, sum(nrows)
INTO tbl, cntr
FROM systabnames AS st,sysptnhdr AS sp
WHERE st.partnum = sp.partnum
AND sp.npdata = 0
AND st.dbname = dbs
GROUP BY 1
ORDER BY 1;
RETURN tbl, cntr WITH RETURN;
END FOREACH;
END PROCEDURE;
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------