DB VERSION

-- engine
SELECT
  ENGINE, COUNT(*)
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sikad_tsm_20210204"
GROUP BY ENGINE;

-- 
SELECT
  ROW_FORMAT, COUNT(*)
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sikad_tsm_20210204"
GROUP BY ROW_FORMAT;

-- order TABLE_ROWS
SELECT
  TABLE_NAME, TABLE_ROWS
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sikad_tsm_20210204"
ORDER BY TABLE_ROWS DESC;

-- TABLE_ROWS
SELECT t.RENTANG, COUNT(*)
FROM (
	SELECT CASE
		WHEN TABLE_ROWS IS NULL THEN 'NULL'
		WHEN TABLE_ROWS = 0 THEN '0'
		WHEN TABLE_ROWS BETWEEN 1 AND 10 THEN '10'
		WHEN TABLE_ROWS BETWEEN 11 AND 100 THEN '100'
		WHEN TABLE_ROWS BETWEEN 101 AND 1000 THEN '1000'
		WHEN TABLE_ROWS BETWEEN 1001 AND 10000 THEN '10000'
		WHEN TABLE_ROWS BETWEEN 10001 AND 100000 THEN '100000'
		WHEN TABLE_ROWS BETWEEN 100001 AND 1000000 THEN '1000000'
		WHEN TABLE_ROWS BETWEEN 1000001 AND 10000000 THEN '10000000'
		ELSE 'LAIN-LAIN'
		END AS RENTANG
	FROM
		information_schema.TABLES
	WHERE
		TABLE_SCHEMA = "sikad_tsm_20210204"
	) t
GROUP BY t.RENTANG
ORDER BY t.RENTANG ASC;

-- 
SELECT
  TABLE_NAME, DATA_LENGTH
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sikad_tsm_20210204"
ORDER BY DATA_LENGTH DESC;

-- DATA_LENGTH
SELECT t.RENTANG, COUNT(*)
FROM (
	SELECT CASE
		WHEN DATA_LENGTH IS NULL THEN 'NULL'
		WHEN DATA_LENGTH = 0 THEN '0'
		WHEN DATA_LENGTH BETWEEN 1 AND 10 THEN '10'
		WHEN DATA_LENGTH BETWEEN 11 AND 100 THEN '100'
		WHEN DATA_LENGTH BETWEEN 101 AND 1000 THEN '1000'
		WHEN DATA_LENGTH BETWEEN 1001 AND 10000 THEN '10000'
		WHEN DATA_LENGTH BETWEEN 10001 AND 100000 THEN '100000'
		WHEN DATA_LENGTH BETWEEN 100001 AND 1000000 THEN '1000000'
		WHEN DATA_LENGTH BETWEEN 1000001 AND 10000000 THEN '10000000'
		WHEN DATA_LENGTH BETWEEN 10000001 AND 100000000 THEN '100000000'
		WHEN DATA_LENGTH BETWEEN 100000001 AND 1000000000 THEN '1000000000'
		ELSE 'LAIN-LAIN'
		END AS RENTANG
	FROM
		information_schema.TABLES
	WHERE
		TABLE_SCHEMA = "sikad_tsm_20210204"
	) t
GROUP BY t.RENTANG
ORDER BY t.RENTANG ASC;


-- INDEX_LENGTH
SELECT
  TABLE_NAME, INDEX_LENGTH
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sikad_tsm_20210204"
ORDER BY INDEX_LENGTH DESC;

-- INDEX_LENGTH
SELECT t.RENTANG, COUNT(*)
FROM (
	SELECT CASE
		WHEN INDEX_LENGTH IS NULL THEN 'NULL'
		WHEN INDEX_LENGTH = 0 THEN '0'
		WHEN INDEX_LENGTH BETWEEN 1 AND 10 THEN '10'
		WHEN INDEX_LENGTH BETWEEN 11 AND 100 THEN '100'
		WHEN INDEX_LENGTH BETWEEN 101 AND 1000 THEN '1000'
		WHEN INDEX_LENGTH BETWEEN 1001 AND 10000 THEN '10000'
		WHEN INDEX_LENGTH BETWEEN 10001 AND 100000 THEN '100000'
		WHEN INDEX_LENGTH BETWEEN 100001 AND 1000000 THEN '1000000'
		WHEN INDEX_LENGTH BETWEEN 1000001 AND 10000000 THEN '10000000'
		WHEN INDEX_LENGTH BETWEEN 10000001 AND 100000000 THEN '100000000'
		WHEN INDEX_LENGTH BETWEEN 100000001 AND 1000000000 THEN '1000000000'
		ELSE 'LAIN-LAIN'
		END AS RENTANG
	FROM
		information_schema.TABLES
	WHERE
		TABLE_SCHEMA = "sikad_tsm_20210204"
	) t
GROUP BY t.RENTANG
ORDER BY t.RENTANG ASC;

-- COLUMNS
SELECT
	TABLE_NAME, COUNT(*) AS JML
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'sikad_tsm_20210204'
GROUP BY TABLE_NAME
ORDER BY JML DESC;

SELECT
  COUNT(*)
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sikad_tsm_20210204";

SELECT t.RENTANG, COUNT(*)
FROM (
	SELECT CASE
		WHEN COUNT(*) IS NULL THEN 'NULL'
		WHEN COUNT(*) = 0 THEN '0'
		WHEN COUNT(*) BETWEEN 1 AND 10 THEN '10'
		WHEN COUNT(*) BETWEEN 11 AND 20 THEN '20'
		WHEN COUNT(*) BETWEEN 21 AND 30 THEN '30'
		WHEN COUNT(*) BETWEEN 31 AND 40 THEN '40'
		WHEN COUNT(*) BETWEEN 41 AND 50 THEN '50'
		WHEN COUNT(*) BETWEEN 51 AND 60 THEN '60'
		WHEN COUNT(*) BETWEEN 61 AND 70 THEN '70'
		WHEN COUNT(*) BETWEEN 71 AND 80 THEN '80'
		WHEN COUNT(*) BETWEEN 81 AND 90 THEN '90'
		WHEN COUNT(*) BETWEEN 91 AND 100 THEN '100'
		ELSE 'Above 100'
		END AS RENTANG
	FROM
		information_schema.COLUMNS
	WHERE
		TABLE_SCHEMA = "sikad_tsm_20210204"
	GROUP BY TABLE_NAME
	) t
GROUP BY t.RENTANG
ORDER BY t.RENTANG ASC;