The total number of fields in all tables in the database

Question

I have a huge database with hundreds of tables, and I want to know the full fields (columns) defined in all tables.

Is there a sql query that can explain this to me? If not, what would be the best way?

Answer 1

Is this what you want?

select count(*) from information_schema.columns where table_schema = 'your_schema' 

You can run it like this to make sure this is reasonable:

 select table_name, column_name from information_schema.columns where table_schema = 'your_schema' order by 1, 2 

Answer 2

Try this (while logging into your current schema):

 select count(*) from information_schema.columns where table_schema = DATABASE(); 

Answer 3

I am new to mysql, but if the table information_schema.columns is a table with table_name and column_name , then you can use the following query

 select table_name, count( distinct column_name ) column_number_used from information_schema.columns where table_schema = 'your_schema' group by table_name 

this should indicate the names of all tables with the corresponding column number used in this table.

Answer 4

Try the following:

 SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 

Answer 5

You can get the queries you need from this open source project called anywhereindb . The end result of this project goes further than you need, but you can look into the code and take out the part where it will display all parts of the tables.

Answer 6

Below is an example that can work and modify queries according to your requirements.

 Use [Your_DB_Name] /* Count Total Number Of Tables */ SELECT COUNT(*) AS 'Total Tables' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' /* Count Total Number Of Views */ SELECT COUNT(*) AS 'Total Views' FROM INFORMATION_SCHEMA.VIEWS /* Count Total Number Of Stored Procedures */ SELECT COUNT(*) AS 'Total SPs' FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' /* Count Total Number Of UDF(User Defined Functions) */ SELECT COUNT(*) AS 'Total Functions' FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' 

Example: