sethu sethu - 1 year ago 65
MySQL Question

Query to find rowcount of a table

I am trying to find the query to find the row and byte count of a table from each backend database:

I) MySQL

II) Oracle DB

III) SQL Server

IV) Mongo DB

V) Teradata

Answer Source

MySQL

Row count of single table

select count(*) from table_name;

Row Count of all tables

SELECT SUM(TABLE_ROWS) 
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_SCHEMA = 'database schema name';

Size
SELECT table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

Result for size would be like this

Database Table              Size in MB
sakai    sakai_realm_rl_fn  22.39
sonar    file_sources       8.56
sakai    sakai_site_tool    4.55
sakai    sakai_event        4.03
sonar    issues             3.75
sakai    sakai_site_page    3.03
sonar    project_measures   2.03

MongoDB In mongoDB we don't have rows and columns, but everything is documents, so we can find the number of documents in a collection and the size of it.

db.collection.stats() // example db.stackoverflow.stats(), where stackoverflow is my collection name

The result would be similar to this

{
        "ns" : "test.stackoverflow",
      "count" : 2,
        "size" : 224,
        "avgObjSize" : 112,
        "numExtents" : 1,
      "storageSize" : 8192,
        "lastExtentSize" : 8192,
        "paddingFactor" : 1,
        "paddingFactorNote" : "paddingFactor is unused and unmaintained in 3.0. It remains hard coded to 1.0 for compatibility only.",
        "userFlags" : 1,
        "capped" : false,
        "nindexes" : 1,
        "totalIndexSize" : 8176,
        "indexSizes" : {
                "_id_" : 8176
        },
        "ok" : 1
}


db.stats(); // Will help us in finding the total documents in the database

Executing it in mongo shell, will a give a result like this

{
        "db" : "test",
        "collections" : 9,
        "objects" : 1000063,
        "avgObjSize" : 112.00058396321032,
        "dataSize" : 112007640,
        "storageSize" : 175837184,
        "numExtents" : 20,
        "indexes" : 12,
        "indexSize" : 169938160,
        "fileSize" : 2080374784,
        "nsSizeMB" : 16,
        "extentFreeList" : {
                "num" : 70,
                "totalSize" : 760217600
        },
        "dataFileVersion" : {
                "major" : 4,
                "minor" : 22
        },
        "ok" : 1
}

Here objects is the number of documents across collections

More info - http://docs.mongodb.com/manual/reference/method/db.stats

Oracle

Row count of single table
select count(*) from table_name

Total Size
select * from dba_data_files;

select round((sum(bytes)/1048576/1024),2) from v$datafile;

To execute these queries you need to login as system user with all priveleges
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download