Query your MySQL server for table size by row count and physical size by database

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
3
down vote

favorite












After writing a similar SQL procedure to check my table sizes several times, I wrote this stored procedure to check my MySQL database's table size. It will show you the table sizes and row count across all tables in a particular database. I use this to identify any scaling issues with my database and review storage.



Stored procedure



DELIMITER //
CREATE PROCEDURE checkTableSize 
(IN tableName VARCHAR(255))
BEGIN
 SELECT 
 table_name AS `Table`, 
 round(avg(((data_length + index_length) / 1024 / 1024)), 2) as `Size in MB`, 
 sum(table_rows) as rowCount 
 FROM information_schema.TABLES where table_schema = tableName 
 GROUP by table_name
 ORDER by rowCount desc;
END //
DELIMITER ;


How to call results



call checkTableSize('EnterDBNameHere');


I'd like to know if there are out-of-the-box solutions from MySQL, or more elegant solutions to my stored procedure. I also thought I'd share in case this helps anyone.




sql mysql database




share|improve this question





















  • There's a single row per table, so why do you apply aggregate functions & GROUP BY?
    – dnoeth
    Jan 29 at 7:39
















up vote
3
down vote

favorite












After writing a similar SQL procedure to check my table sizes several times, I wrote this stored procedure to check my MySQL database's table size. It will show you the table sizes and row count across all tables in a particular database. I use this to identify any scaling issues with my database and review storage.



Stored procedure



DELIMITER //
CREATE PROCEDURE checkTableSize 
(IN tableName VARCHAR(255))
BEGIN
 SELECT 
 table_name AS `Table`, 
 round(avg(((data_length + index_length) / 1024 / 1024)), 2) as `Size in MB`, 
 sum(table_rows) as rowCount 
 FROM information_schema.TABLES where table_schema = tableName 
 GROUP by table_name
 ORDER by rowCount desc;
END //
DELIMITER ;


How to call results



call checkTableSize('EnterDBNameHere');


I'd like to know if there are out-of-the-box solutions from MySQL, or more elegant solutions to my stored procedure. I also thought I'd share in case this helps anyone.




sql mysql database




share|improve this question





















  • There's a single row per table, so why do you apply aggregate functions & GROUP BY?
    – dnoeth
    Jan 29 at 7:39












up vote
3
down vote

favorite









up vote
3
down vote

favorite











After writing a similar SQL procedure to check my table sizes several times, I wrote this stored procedure to check my MySQL database's table size. It will show you the table sizes and row count across all tables in a particular database. I use this to identify any scaling issues with my database and review storage.



Stored procedure



DELIMITER //
CREATE PROCEDURE checkTableSize 
(IN tableName VARCHAR(255))
BEGIN
 SELECT 
 table_name AS `Table`, 
 round(avg(((data_length + index_length) / 1024 / 1024)), 2) as `Size in MB`, 
 sum(table_rows) as rowCount 
 FROM information_schema.TABLES where table_schema = tableName 
 GROUP by table_name
 ORDER by rowCount desc;
END //
DELIMITER ;


How to call results



call checkTableSize('EnterDBNameHere');


I'd like to know if there are out-of-the-box solutions from MySQL, or more elegant solutions to my stored procedure. I also thought I'd share in case this helps anyone.




sql mysql database




share|improve this question













After writing a similar SQL procedure to check my table sizes several times, I wrote this stored procedure to check my MySQL database's table size. It will show you the table sizes and row count across all tables in a particular database. I use this to identify any scaling issues with my database and review storage.



Stored procedure



DELIMITER //
CREATE PROCEDURE checkTableSize 
(IN tableName VARCHAR(255))
BEGIN
 SELECT 
 table_name AS `Table`, 
 round(avg(((data_length + index_length) / 1024 / 1024)), 2) as `Size in MB`, 
 sum(table_rows) as rowCount 
 FROM information_schema.TABLES where table_schema = tableName 
 GROUP by table_name
 ORDER by rowCount desc;
END //
DELIMITER ;


How to call results



call checkTableSize('EnterDBNameHere');


I'd like to know if there are out-of-the-box solutions from MySQL, or more elegant solutions to my stored procedure. I also thought I'd share in case this helps anyone.





sql mysql database





share|improve this question












share|improve this question




share|improve this question








edited Jan 28 at 22:06









Jamal♦

30.1k11114225




30.1k11114225









asked Jan 28 at 16:58









Dom

1263




1263











  • There's a single row per table, so why do you apply aggregate functions & GROUP BY?
    – dnoeth
    Jan 29 at 7:39
















  • There's a single row per table, so why do you apply aggregate functions & GROUP BY?
    – dnoeth
    Jan 29 at 7:39















There's a single row per table, so why do you apply aggregate functions & GROUP BY?
– dnoeth
Jan 29 at 7:39




There's a single row per table, so why do you apply aggregate functions & GROUP BY?
– dnoeth
Jan 29 at 7:39















active

oldest

votes











Your Answer




StackExchange.ifUsing("editor", function ()
return StackExchange.using("mathjaxEditing", function ()
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix)
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
);
);
, "mathjax-editing");

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "196"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);








 

draft saved


draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f186208%2fquery-your-mysql-server-for-table-size-by-row-count-and-physical-size-by-databas%23new-answer', 'question_page');

);

Post as a guest






















active

oldest

votes













active

oldest

votes









active

oldest

votes






active

oldest

votes










 

draft saved


draft discarded


























 


draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f186208%2fquery-your-mysql-server-for-table-size-by-row-count-and-physical-size-by-databas%23new-answer', 'question_page');

);

Post as a guest
































































-

Popular posts from this blog

Python Lists

<!-- main_leaderboard, all: [728,90][970,90][320,50][468,60] --> Python Lists ❮ Previous Next ❯ Python Collections (Arrays) There are four collection data types in the Python programming language: List is a collection which is ordered and changeable. Allows duplicate members. Tuple is a collection which is ordered and unchangeable. Allows duplicate members. Set is a collection which is unordered and unindexed. No duplicate members. Dictionary is a collection which is unordered, changeable and indexed. No duplicate members. When choosing a collection type, it is useful to understand the properties of that type. Choosing the right type for a particular data set could mean retention of meaning, and, it could mean an increase in efficiency or security. List A list is a collection which is ordered and changeable. In Python lists are written with square brackets. Example Create a List: thislist = ["apple", "banana", ...
Read more

Aion

Image
Aion - 6+ , 5417 Aion - . MMORPG, World of Warcraft, World of Tanks My Lineage II . CryEngine. , . : Aion https://ru.4game.com/aion/ Aion , . , , . , , . , , , . . , , , . . : . , . , , . , , . 12 , 2 : ( ). ( ). ( ). ( ) ( ). ( ). , . , : . . , . , , . . , , . Aion, , . , . , . : . . . . , : , , , , , . , , , . : Aion https://ru.4game.com/aion/install/ : . , . , . , Aion . , . . , . , . , , . . : . . . , ( !), MMORPG Aion . , . ...
Read more

JavaScript Array Iteration Methods

<!-- main_leaderboard, all: [728,90][970,90][320,50][468,60] --> JavaScript Array Iteration Methods ❮ Previous Next ❯ Array iteration methods operate on every array item. Array.forEach() The forEach() method Calls a function once for each array element. Example var txt = ""; var numbers = [4, 9, 16, 25]; numbers.forEach(myFunction); function myFunction(value, index, array) {     txt = txt + item + "<br>"; } Try it Yourself » Note that the function takes 3 arguments: The item value The item index The array itself Array.forEach() is not supported in Internet Explorer 8 or earlier: Method forEach() Yes 9.0 Yes Yes Yes Array.map() The map() method creates a new array by performing a function on each array element. This example multiplies each array value by 2: Example var numbers1 = [4, 9, 16, 25]; var numbers2 = numbers1.map(myFunction); function m...
Read more