Getting number of fields in a database with an SQL Statement?

database, field, get, sql

Solution

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

Problem

Original source