mysql : database and table's - memory and data informations
Here I mentioned some basic SQL query
for getting data information in DB and Tables level.
For getting all database size and remaining space available in MB
Query:
SELECT table_schema "Data Base
Name",
ROUND(SUM( data_length + index_length ) / 1024 / 1024 ,2) "Data
Base Size in MB",
ROUND(SUM( data_free )/ 1024 / 1024,2) "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;
OUTPUT:
Data Base Name Data
Base Size in MB Free Space in MB
------------------ -------------------- ------------------
information_schema 0.01 0.00
test1 936.48 100806.00
test2 494.83 100806.00
test3 592.59 106515.59
mysql 0.66 0.01
|
For getting all tables space on specific DB
Query:
SELECT table_name AS
"Tables",
ROUND(((data_length + index_length) /
1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = "test2"
ORDER BY (data_length +
index_length) DESC;
OUTPUT:
Tables Size in MB
------------------ ------------
Table1 512.22
Table2 199.52
Table3 149.42
Table4 36.36
|
Getting table count in specific database
Query for table count:
SELECT COUNT(*) "Total
Tables" FROM information_schema.tables WHERE table_schema = '<dadabase
name>';
OUTPUT:
Total tables
--------------
131
|
Query for table count on each DB:
SELECT IFNULL(table_schema,'Total')
"Database",TableCount
FROM (SELECT COUNT(1)
TableCount,table_schema
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')
GROUP BY table_schema WITH ROLLUP) A;
OUTPUT:
Database TableCount
------------- ------------
<dadabase name> 131
<dadabase name>2 122
|
Getting total rows count in specific database
Query for row count on specific database:
SELECT SUM(TABLE_ROWS) "Total
Table Rows" FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '<dadabase
name>';
OUTPUT:
Total Table Rows
------------------
3298018
Query
for row count on each table:
SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '<dadabase name>' ORDER BY table_rows DESC;
OUTPUT:
table_name table_rows
----------------------------- ------------
templatetrackerdetailmtb 2155754
styledetailmtb 990936
stylemtb 45794
txnmtb 15591
pageflowdetailmtb 14206
|
View all constraints in database
Query:
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Query for specific constraints:
SELECT * FROM
information_schema.TABLE_CONSTRAINTS
WHERE
information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'
|
Reference: stackoverflow
Comments
Post a Comment