INFORMATION SCHEMA (also referred as system catalog) gives you access to database metadata and MySQL server information like the name of a database or table, the data type of a column, and access privileges.
We’ll look at how to get total number of views and tables from the database.
— Get all Tables and Views Count from a specific database
SELECT a.TABLE_SCHEMA, a.TABLE_TYPE, COUNT(*)
FROM INFORMATION_SCHEMA.TABLE a
JOIN INFORMATION_SCHEMA.VIEWS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
WHERE a.TABLE_SCHEMA = ‘sometest’
GROUP BY a.TABLE_SCHEMA, a.TABLE_TYPE;
— Get all Views Count from a specific database
SELECT TABLE_SCHEMA, COUNT(TABLE_NAME)
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = ‘TestDB’
GROUP BY TABLE_SCHEMA;
— Get all Tables Count from a specific database
SELECT TABLE_SCHEMA, COUNT(TABLE_NAME)
FROM information_schema.tables
WHERE TABLE_TYPE = ‘BASE TABLE’
AND TABLE_SCHEMA = ‘TestDB’
GROUP BY TABLE_SCHEMA;
Happy learning!!
Big Data and SQL 