snippets – AgileAdam.com

Show All Tables By Size in MB

SELECT table_name AS "Tables", ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = DATABASE() ORDER BY (data_length + index_length) DESC;

1	SELECT table_name AS "Tables", ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = DATABASE() ORDER BY (data_length + index_length) DESC;

Show All Tables By Size in MB (if > 1 MB)

SELECT table_name AS "Tables", ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = DATABASE() AND (data_length + index_length) > 1048576 ORDER BY (data_length + index_length) DESC;

1	SELECT table_name AS "Tables", ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = DATABASE() AND (data_length + index_length) > 1048576 ORDER BY (data_length + index_length) DESC;

Show All Tables By Number of Rows

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() ORDER BY table_rows DESC;

1	SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() ORDER BY table_rows DESC;

The following snippet is something I used recently to add (below the current item in a menu) links to any anchors found in the content.

$("#content a[name]").each(function() {
  if ($("#page-anchors").length === 0) {
    $(".menu-block-1 li.active:last").append("<ul id='page-anchors'>");
  }
  var aname = $(this).attr("name");
  $("#page-anchors").append($("<li><a href='#" + aname + "'>" + aname + "</a></li>"));
});

$("#content a[name]").each(function() {

if ($("#page-anchors").length === 0) {

$(".menu-block-1 li.active:last").append("<ul id='page-anchors'>");

}

var aname = $(this).attr("name");

$("#page-anchors").append($("<li><a href='#" + aname + "'>" + aname + "</a></li>"));

});

Honestly I could probably create the <ul> outside of the each() loop and save having to check whether it exists with each iteration. Also, it’s probably not necessary to create the aname variable, but the overhead here is minimal.

Snippet: MySQL table sizes ordered by largest to smallest (in MB and Row Count)

Add Links to Anchors Automatically