Find out Number of Objects and Tables count on MariaDB/MySQL

Generates real row counts (COUNT(*)) for all user tables.

Collects counts of objects per database, including:

• Tables

• Views

• Triggers

• Procedures

• Functions

• Events

 DELIMITER $$

-- Drop if exists

DROP PROCEDURE IF EXISTS GetDBObjectsAndRowCounts $$

-- Create procedure

CREATE PROCEDURE GetDBObjectsAndRowCounts()

BEGIN

    DECLARE done INT DEFAULT FALSE;

    DECLARE db_name VARCHAR(64);

    DECLARE tbl_name VARCHAR(64);

    -- Cursor to loop through tables

    DECLARE cur CURSOR FOR

        SELECT table_schema, table_name

        FROM information_schema.tables

        WHERE table_type = 'BASE TABLE'

          AND table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Temp table for real row counts

    DROP TEMPORARY TABLE IF EXISTS temp_row_counts;

    CREATE TEMPORARY TABLE temp_row_counts (

        db_name VARCHAR(64),

        tbl_name VARCHAR(64),

        row_count BIGINT

    );

    -- Temp table for object summary

    DROP TEMPORARY TABLE IF EXISTS temp_object_summary;

    CREATE TEMPORARY TABLE temp_object_summary (

        db_name VARCHAR(64),

        tables INT,

        views INT,

        triggers INT,

        procedures INT,

        functions INT,

        events INT

    );

    -- Insert object counts

    INSERT INTO temp_object_summary (db_name, tables, views, triggers, procedures, functions, events)

    SELECT 

        ts.table_schema,

        COUNT(CASE WHEN ts.table_type = 'BASE TABLE' THEN 1 END) AS table_count,

        COUNT(CASE WHEN ts.table_type = 'VIEW' THEN 1 END) AS view_count,

        (SELECT COUNT(*) FROM information_schema.triggers t WHERE t.trigger_schema = ts.table_schema) AS trigger_count,

        (SELECT COUNT(*) FROM information_schema.routines r WHERE r.routine_schema = ts.table_schema AND r.routine_type = 'PROCEDURE') AS proc_count,

        (SELECT COUNT(*) FROM information_schema.routines r WHERE r.routine_schema = ts.table_schema AND r.routine_type = 'FUNCTION') AS func_count,

        (SELECT COUNT(*) FROM information_schema.events e WHERE e.event_schema = ts.table_schema) AS event_count

    FROM 

        information_schema.tables ts

    WHERE 

        ts.table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')

    GROUP BY ts.table_schema;

    -- Loop through all user tables to get real row counts

    OPEN cur;

    read_loop: LOOP

        FETCH cur INTO db_name, tbl_name;

        IF done THEN

            LEAVE read_loop;

        END IF;

        SET @sql = CONCAT(

            'INSERT INTO temp_row_counts ',

            'SELECT ''', db_name, ''', ''', tbl_name, ''', COUNT(*) FROM `', db_name, '`.`', tbl_name, '`'

        );

        PREPARE stmt FROM @sql;

        EXECUTE stmt;

        DEALLOCATE PREPARE stmt;

    END LOOP;

    CLOSE cur;

    -- Show object summary

    SELECT '=== OBJECT COUNTS BY DATABASE ===' AS section;

    SELECT * FROM temp_object_summary ORDER BY db_name;

    -- Show row counts

    SELECT '=== TABLE ROW COUNTS ===' AS section;

    SELECT * FROM temp_row_counts ORDER BY db_name, tbl_name;

END $$

DELIMITER ;

-- ✅ Run the procedure

CALL GetDBObjectsAndRowCounts();

-- ✅ Drop the procedure

DROP Procedure GetDBObjectsAndRowCounts;

📋 What It Outputs

1. First result set → Object counts per database

2. Second result set → Accurate row counts for all tables