How to create a Stored Procedure to count the rows of each table in a database?
Answered: amber (hollow confidence) — Several correct options: dynamic SQL is not available on 7.31, so read ti_nrows from sysmaster, generate the count statements with UNLOAD, or use Art's sysmaster procedure; the asker did not respond.
Advisory only.
Posted in 2023
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.
Topics: Stored Procedures & SPL, Data Types & Schema Design, Jobs, Consulting & Announcements
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
------------------------------