CoderZone.org

Category: >> mySQL >> Get database and table sizes Bookmark and Share

<< lastnext >>

Snippet Name: Get database and table sizes

Description: Very useful for checking the size of a database or specific tables in a database. For example, you may want to limit the size of a specific user's database or a certain table in his or her schema. This code will let you check the total size of the DB, all tables, and specific tables.

Also see:
» Simple image resize on mouseover
» mySQL Dtatabase Quota Limit Script
» Print X Column Tables with PHP
» Resize and convert an image to a P...
» Select text in a DIV, SPAN, or tab...
» Get Table Row Data On Click
» Get File Size Over HTTP
» Make alternating color table rows ...
» Search and Replace On Every Field ...
» Limit Text Area Characters
» Highlight table rows on rollover
» Dynamically Add/Remove rows in HTM...
» LOCKS: Table Locking
» TABLESPACE: Show contiguous space
» TABLESPACE: SYSAUX tablespace
» TABLESPACE: Tablespace management
» TABLESPACE: List tablespaces, fil...
» TABLESPACE: Dropping Tablespaces
» TABLESPACE: Alter Permanent Tables...
» TABLESPACE: Transportable tablespa...
» TABLESPACE: Tempfile operations
» TABLESPACE: Create temp tablespace
» TABLESPACE: Change UNDO tablespace
» TABLESPACE: Undo Tablespace
» TABLESPACE: SYSAUX Tablespace
» TABLESPACE: Set default tablespace...
» TABLESPACE: Oracle Managed Auto-ex...
» TABLESPACE: Permanent Tablespace U...
» TABLE - Constraints

Comment: (none)

Author:
Language: MYSQL
Highlight Mode: MYSQL
Last Modified: January 09th, 2011

 
-- list the sizes of all of the database tables:
 
SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb") AS Size FROM INFORMATION_SCHEMA.TABLES;
 
 
-- In following 2 examples, replace 'YOUR_DB_NAME' 
-- with the actual name of your database:
 
-- Get the size of specific tables in the database:
 
SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size 
FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%YOUR_DB_NAME%' ;
 
 
--Get the total size of the entire database:
 
SELECT 
CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size 
FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%YOUR_DB_NAME%' ;
 
 
-- This is used to convert to Mb and make the output more readable:
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb") 
 
 
-- This gives actual size in bytes:
DATA_LENGTH + INDEX_LENGTH - DATA_FREE
 
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 9