CODEWITHSUNDEEP
mysqlsqldatabase
zaf
zaf

Reputation: 23244

Total number of fields in all tables in database

I have a huge database with hundreds of tables and I want to find out the total fields (columns) defined in all of the tables.

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

Upvotes: 6

Views: 5119

Answers (6)

Bhaumik Patel
Bhaumik Patel

Reputation: 15696

Below example may works and modify queries as per your requirement.

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:

enter image description here

Upvotes: 0

pratik garg
pratik garg

Reputation: 3342

I am new in mysql but if table information_schema.columns is the table with table_name and column_name information then you can use 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 give all table names with respective column number used in that table..

Upvotes: 1

Andrew Jackman
Andrew Jackman

Reputation: 13966

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

Upvotes: 0

Michael De Silva
Michael De Silva

Reputation: 3818

Try this:

SELECT COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS

Upvotes: 0

MikeTheReader
MikeTheReader

Reputation: 4190

Try this (while logged into your current schema):

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

Upvotes: 3

MJB
MJB

Reputation: 7686

Is this what you want?

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

You can run it like this to see if it is reasonable:

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

Upvotes: 4

Related Questions