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.
Each year we see some changes in the positioning of the most popular databases on the market. “The most popular” part of that sentence can be the most difficult to judge. There are lots and lots of different opinions on this and ways of judging them. There are various sites giving league tables, and even with those some people don’t agree with how they perform their rankings.
The following table contains links for some of the main Database engines including download pages, social media links, community support sites and to the documentation.
I’ve previously written about using the Python pytrends package to explore the relative importance of the different Database engines. The results from pytrends gives results based on number of searches etc in Google. Check out that Blog Post. I’ve rerun the same code for 2021, and the following gallery displays charts for each Database based on their popularity. This will allow you to see what countries are most popular for each Database and how that relates to the other databases. For these charts I’ve included Oracle, MySQL, SQL Server, PostgreSQL and MongoDB, as these are the top 5 Databases from DB-Engines.