Pages

Wednesday, August 25, 2010

MySQL:Information schema Tips

Query to list largest tables in the database
------------------------------------------------------------

mysql> SELECT concat(table_schema,'.',table_name) table_name,

concat(round(data_length/(1024*1024),2),'M') data_length

FROM information_schema.TABLES

ORDER BY data_length DESC;



Query to list all tables without primary key
---------------------------------------------------------

SELECT CONCAT(t.table_name,".",t.table_schema) as table_name

FROM information_schema.TABLES t

LEFT JOIN information_schema.TABLE_CONSTRAINTS tc

ON t.table_schema = tc.table_schema

AND t.table_name = tc.table_name

AND tc.constraint_type = 'PRIMARY KEY'

WHERE tc.constraint_name IS NULL

AND t.table_type = 'BASE TABLE';

Query to display tables with more than 1000 rows
--------------------------------------------------------------------

mysql> select concat(table_schema,'.',table_name) as table_name,table_rows

from information_schema.tables where table_rows > 1000

order by table_rows desc;





INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS shows the primary key-foreign key
----------------------------------------------------------------------------
SELECT table_name, referenced_table_name, update_rule, delete_rule
FROM information_schema.referential_constraints
WHERE constraint_schema='databasename'
ORDER BY table_name;




INFORMATION_SCHEMA.TABLE_CONSTRAINTS reveals the constraints defined in tables.
----------------------------------------------------------------------------------
SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_schema='databasename'
ORDER BY table_name, constraint_type;


INFORMATION_SCHEMA.COLUMN_PRIVILEGES view to get this information.
------------------------------------------------------------------
SELECT table_name, column_name, grantee, privilege_type, is_grantable
FROM information_schema.column_privileges
WHERE table_schema='databasename'
ORDER BY table_name




SELECT table_name, grantee, privilege_type, is_grantable
FROM information_schema.table_privileges
WHERE table_schema='databasename'
ORDER BY table_name;


Cheers !!!