CODEWITHSUNDEEP
mysql
sridharnetha
sridharnetha

Reputation: 2248

How to count number of columns and rows in mysql

I have a requirement where i need to count number of columns, number of rows, and size of each table for a given database.

The query below is not giving the exact result,

SELECT t1.table_name, t1. table_rows,COUNT(t2.table_name) AS table_colums, t1. data_length 
FROM INFORMATION_SCHEMA.TABLES t1 
JOIN (SELECT table_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='my_db_name') t2 
ON t1.table_name=t2.table_name 
WHERE t1.TABLE_SCHEMA = 'my_db_name';

Any clues?

Upvotes: 2

Views: 167

Answers (3)

Inaam
Inaam

Reputation: 488

This might get you the required result

SELECT t1.table_name
,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
,(select count(*) from information_schema.columns c where c.table_schema = t1.table_schema and c.table_name = t1.table_name) as cols
,t1.table_rows as rows
FROM INFORMATION_SCHEMA.TABLES t1 
WHERE t1.table_schema = 'my_db_name'

Upvotes: 0

Socrates
Socrates

Reputation: 9574

Counting rows:

SELECT COUNT(*) FROM yourtable;

Counting columns:

SELECT count(*)
FROM information_schema.columns
WHERE table_name = 'yourtable'

If you mean by size of each table the size in MB, then:

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "yourdatabase"
    AND table_name = "yourtable";

Upvotes: 2

sridharnetha
sridharnetha

Reputation: 2248

This query solved my problem.

SELECT t1.table_name, t1.table_rows, 
(SELECT COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'my_db_name'
AND table_name = t1.table_name) AS colsm, 
t1.data_length
FROM INFORMATION_SCHEMA.TABLES t1
WHERE t1.TABLE_SCHEMA = 'my_db_name';

Upvotes: 0

Related Questions