META-DATA queries in MYSQL

1.) display all the table name and tableschemas
select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,TABLE_ROWS from information_Schema.tables;

2) displaying the table name and the column name
select TABLE_SCHEMA,TABLE_NAME, group_concat(COLUMN_NAME) colname,count(COLUMN_NAME) cnt  from information_schema.columns
where table_schema='test' group by TABLE_SCHEMA,TABLE_NAME ;
3) dislaying all the views and their structure .
select TABLE_SCHEMA,TABLE_NAME from information_schema.views where table_Schema like 'test'
select TABLE_SCHEMA,TABLE_NAME,VIEW_DEFINITION from information_schema.views where table_Schema like 'test' \G
4. display all the triggers and their structure
select TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_Timing,ACTION_STATEMENT from information_Schema.triggers \G
 select TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_Timing from information_Schema.triggers;
5. indexes
select table_schema,table_name,index_name,index_type,column_name from information_schema.STATISTICS where table_schema='test';
select table_schema,table_name,COLUMN_NAME,index_name,SEQ_IN_INDEX,NON_UNIQUE from information_schema.STATISTICS where table_schema='test';
select table_schema,table_name,COLUMN_NAME,group_concat(index_name),count(index_name) from information_schema.STATISTICS where table_schema='test' group by table_schema,table_name,COLUMN_NAME;
6.) routines
select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_BODY from information_Schema.routines;
select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_BODY,Routine_definition from information_Schema.routines;
7.) Primary key and their foreign  keys
SELECT CONCAT( table_name, '.', column_name, ' -> ', referenced_table_name, '.', referenced_column_name ) AS list_of_fks  FROM INFORMATION_SCHEMA.key_column_usage  WHERE referenced_table_schema = 'test' AND referenced_table_name IS NOT NULL  ORDER BY table_name, column_name;

8) count all the table and column count and record count
mysqlshow -u kiran -pkiran@123  test --count
mysqlshow -u kiran -pkiran@123  --count

Getting errors while revoking table level privileges

mysql> show grants for 'kiran'@'localhost';
>+-------------------------------------------------------------------------------------------------------------------------------------------------------+
>| Grants for kiran@localhost |
>+-------------------------------------------------------------------------------------------------------------------------------------------------------+
 GRANT USAGE ON *.* TO 'kiran'@'localhost' IDENTIFIED BY PASSWORD '*C57074CED5D2747BA86351977028B255F1AD6914' |
 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `Database1`.* TO 'kiran'@'localhost' |
 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `Database2`.* TO 'kiran'@'localhost' |


mysql> REVOKE UPDATE ON Database1.transaction FROM 'kiran'@'localhost';
ERROR 1147 (42000): There is no such grant defined for user 'kiran' on host 'localhost' on table 'transaction'


### Giving privileges on each table based on schema

SELECT CONCAT( 'GRANT SELECT, INSERT, XXX ON ', TABLE_SCHEMA, '.', TABLE_NAME, " ON `Database1`.* TO 'kiran'@'localhost'" ) FROM TABLES WHERE TABLE_SCHEMA='Database1';

## giving privileges on all schema and all tables

mysql -Bse"select CONCAT(\"GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, UPDATE ON \",TABLE_SCHEMA,\".\",TABLE_NAME, \" TO 'kiran'@'localhost' identified by 'kiran';\") from information_schema.tables where TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')"

## giving privileges on all schemas ( schema level privileges)
mysql -Bse"select CONCAT(\"GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, UPDATE ON \",SCHEMA_NAME,\".*\" \" TO 'kiran'@'localhost' identified by 'kiran';\") from information_schema.SCHEMATA where SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema')"


For more details about the privileges the url is as follows:

http://dev.mysql.com/doc/refman/5.5/en/grant.html#grant-database-privileges
http://dev.mysql.com/doc/refman/5.5/en/grant.html#grant-table-privileges