CODEWITHSUNDEEP
sqldatabasegetfield
Ash
Ash

Reputation: 9081

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

How would I get the number of fields/entries in a database using an SQL Statement?

Upvotes: 13

Views: 25631

Answers (5)

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

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

Upvotes: 30

hye ji
hye ji

Reputation: 1

select count(column_name) from information_schema.columns 
where table_name = **name of your table here **

Upvotes: 0

jcansell
jcansell

Reputation: 164

Just for any other readers who are googling...

There are several non-SQL solutions, that may be useful to the user.. here's 2 that I use.

Example 1: Access VBA:

'Microsoft Access VBA
Function Count_Fields(Table_Name As String) As Integer
    Dim myFieldCount As Integer
    Dim db As DOA.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset(Table_Name, dbOpenDynaset)
    myFieldCount = rs.Fields.Count
    'return the count
    Count_Fields = myFieldCount
    'tidy up
    Set rs = Nothing
    Set db = Nothing
End Function

Example 2: PHP 5.1:

    <?php
    // PHP5 Implementation - uses MySQLi.
    function countFields ($tableName) { 
    $db = new mysqli('myserver.me.com', 'user' ,'pass', 'databasename');
    if(!$db) {
        echo 'ERROR: Could not connect to the database.';
        } 
    else {
        $rs->$db->query("SELECT * FROM ".$tableName.");
        $fieldCount = $rs->field_count;
        }
    return $fieldCount;
?>

please excuse any typo's in the above - hope someone finds this useful

Upvotes: 0

Scott Ivey
Scott Ivey

Reputation: 41568

Sounds like this is what you need.

select CountOfFieldsInDatabase = count(*)
from   information_schema.columns

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135011

try this, this will exclude views, leave the where clause out if you want views

  select count(*) from information_schema.columns c
join information_schema.tables t on c.table_name = t.table_name
and t.table_type = 'BASE TABLE'

Upvotes: 4

Related Questions