Menu
How would I get the number of fields/entries in a database using an SQL Statement?
777
Get number of fields in MySQL table? To display number of fields in MySQL, use the COUNT (*). Following is the syntax −
Get list of all the tables and the fields in database: Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_CATALOG Like 'DatabaseName' Get list of all the fields in table: Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_CATALOG Like 'DatabaseName' And TABLE_NAME Like 'TableName'
The query is, Here, the table has 6 columns in it. To see the table use the ‘ DESC table_name ‘ query. If we use a Microsoft SQL server then we need to use ‘EXEC sp_help’ in place of DESC.
SQL COUNT (*) example. To get the number of rows in the employees table, you use the COUNT (*) function table as follows: SELECT COUNT (*) FROM employees; See it in action. To find how many employees who work in the department id 6, you add the WHERE clause to the query as follows: SELECT COUNT (*) FROM employees WHERE department_id = 6;
mmm all the fields in all the tables? assuming standards (mssql, mysql, postgres) you can issue a query over information_schema.columns
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
Or grouped by table:
SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
If multiple schemas has the same table name in the same DB, you MUST include schema name as well (i.e: dbo.Books, user.Books, company.Books etc.) Otherwise you'll get the wrong results. So the best practice is:
SELECT TABLE_SCHEMA, TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_SCHEMA, TABLE_NAME
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
