A possible common task developers perform is to find out how many records exists in every table in a schema. In the examples below I’ll show examples for the current schema of the developer, but these can be expanded easily to include tables in other schemas or for all schemas across a database.
These example include the different ways of determining this information across the main databases including Oracle, MySQL, Postgres, SQL Server and Snowflake.
A little warning before using these queries. They may or may not give the true accurate number of records in the tables. These examples illustrate extracting the number of records from the data dictionaries of the databases. This is dependent on background processes being run to gather this information. These background processes run from time to time, anything from a few minutes to many tens of minutes. So, these results are good indication of the number of records in each table.
SELECT table_name, num_rows
ORDER BY num_rows DESC;
SELECT table_name, to_number( extractvalue(xmltype( dbms_xmlgen.getxml('select count(*) c from '||table_name)) ,'/ROWSET/ROW/C')) tab_count FROM user_tables ORDER BY tab_count desc;
Using PL/SQL we can do something like the following.
DECLARE val NUMBER; BEGIN FOR i IN (SELECT table_name FROM user_tables ORDER BY table_name desc) LOOP EXECUTE IMMEDIATE 'SELECT count(*) FROM '|| i.table_name INTO val; DBMS_OUTPUT.PUT_LINE(i.table_name||' -> '|| val ); END LOOP; END;
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' AND TABLE_SCHEMA = current_user();
Using Common Table Expressions (CTE), using WITH clause
WITH table_list AS ( SELECT table_name FROM information_schema.tables WHERE table_schema = current_user() AND table_type = 'BASE TABLE' ) SELECT CONCAT( GROUP_CONCAT(CONCAT("SELECT '",table_name,"' table_name,COUNT(*) rows FROM ",table_name) SEPARATOR " UNION "), ' ORDER BY table_name' ) INTO @sql FROM table_list;
select relname as table_name, n_live_tup as num_rows from pg_stat_user_tables;
An alternative is
select n.nspname as table_schema, c.relname as table_name, c.reltuples as rows from pg_class c join pg_namespace n on n.oid = c.relnamespace where c.relkind = 'r' and n.nspname = current_user order by c.reltuples desc;
SELECT tab.name, sum(par.rows) FROM sys.tables tab INNER JOIN sys.partitions par ON tab.object_id = par.object_id WHERE schema_name(tab.schema_id) = current_user
SELECT t.table_schema, t.table_name, t.row_count FROM information_schema.tables t WHERE t.table_type = 'BASE TABLE' AND t.table_schema = current_user order by t.row_count desc;
The examples give above are some of the ways to obtain this information. As with most things, there can be multiple ways of doing it, and most people will have their preferred one which is based on their background and preferences.
As you can see from the code given above they are all very similar, with similar syntax, etc. The only thing different is the name of the data dictionary table/view containing the information needed. Yes, knowing what data dictionary views to query can be a little challenging as you switch between databases.