DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Extract Info From Information_schema

10.22.2011
| 3178 views |
  • submit to reddit
        // Finding stats using tables from information_schema database. Last 2 queries have been borrowed from
// http://forge.mysql.com/tools/tool.php?id=119

-- Total rows and size
SELECT COUNT(*) TABLES,
       CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
       CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
       CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
       CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
       ROUND(SUM(index_length) / SUM(data_length),2)  idxfrac
FROM   information_schema.TABLES 

-- Database wise report
SELECT COUNT(*) TABLES, table_schema,
         CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
         CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
         CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
         CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
         ROUND(SUM(index_length) / SUM(data_length),2)  idxfrac
FROM     information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC 

-- Engine wise breakup
SELECT ENGINE, COUNT(*) TABLES,
       CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
       CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
       CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
       CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
       ROUND(SUM(index_length) / SUM(data_length),2)  idxfrac
FROM   information_schema.TABLES 
GROUP BY engine ORDER BY sum(data_length+index_length) DESC

-- Top 30 tables
SELECT table_schema, table_name, engine, table_rows,
         CONCAT(ROUND(data_length / (1024 * 1024),2),'MB') AS DATA,
         CONCAT(ROUND((data_length + index_length) / (1024 * 1024), 2),'MB') AS total_size
FROM     INFORMATION_SCHEMA.TABLES
ORDER BY data_length DESC LIMIT 30

-- List of Full Text Indexes
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY, NULLABLE
FROM     statistics
WHERE    index_type LIKE 'FULLTEXT%' ORDER BY TABLE_SCHEMA, TABLE_NAME 

-- The number of columns for each datatype
SELECT DATA_TYPE, COUNT(*) AS mycount
FROM     `COLUMNS`
WHERE    TABLE_SCHEMA NOT IN ('information_schema','mysql') 
GROUP BY DATA_TYPE ORDER BY mycount DESC

-- The number of columns for each datatype with size (replace my_database)
SELECT TABLE_SCHEMA, COLUMN_TYPE, count(*) FROM `COLUMNS` 
GROUP BY COLUMN_TYPE HAVING TABLE_SCHEMA = 'my_database'

-- Blob, Float and Double Data types details
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT,
         IS_NULLABLE, NUMERIC_PRECISION, COLUMN_TYPE, COLUMN_KEY
FROM     COLUMNS
WHERE    DATA_TYPE IN ('FLOAT','BLOB','DOUBLE')
AND TABLE_SCHEMA != 'mysql'
ORDER BY DATA_TYPE, COLUMN_TYPE

--All tables with no primary key

use INFORMATION_SCHEMA;
select CONCAT(t.table_name,".",t.table_schema) as tbl,
from INFORMATION_SCHEMA.TABLES AS t 
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
ON (t.TABLE_NAME=c.TABLE_NAME
 AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA
 AND constraint_name='PRIMARY')
 WHERE t.table_schema!="information_schema"
 AND constraint_name IS NULL;

-- All tables and their primary keys, if exist:

use INFORMATION_SCHEMA;
select CONCAT(t.table_name,".",t.table_schema) as tbl,
c.column_name,c.constraint_name
from INFORMATION_SCHEMA.TABLES AS t 
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
ON (t.TABLE_NAME=c.TABLE_NAME
 AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA
 AND constraint_name='PRIMARY')
 WHERE t.table_schema!="information_schema"
order by constraint_name;


-- Foreign Key constraints for Database dbName

SELECT A.TABLE_SCHEMA AS FKTABLE_SCHEM, A.TABLE_NAME AS FKTABLE_NAME, A.COLUMN_NAME AS FKCOLUMN_NAME, 
A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM, A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, 
A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, A.CONSTRAINT_NAME AS FK_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A, INFORMATION_SCHEMA.TABLE_CONSTRAINTS B 
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME 
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL 
HAVING PKTABLE_SCHEM IS NOT NULL 
and A.TABLE_SCHEMA = 'dbName' 
ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION limit 1000