Query to count the number of columns in a table:
select count(*) from user_tab_columns where table_name = 'tablename';
Replace tablename with the name of the table whose total number of columns you want returned.
SELECT count(*) FROM information_schema.`COLUMNS` C
WHERE table_name = 'your_table_name'
AND TABLE_SCHEMA = "your_db_name"
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_catalog = 'database_name' -- the database
AND table_name = 'table_name'
Following query finds how columns in table:-
SELECT COUNT(COLUMN_NAME) FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TableName';
SELECT count(*)
FROM information_schema.columns
WHERE table_name = 'Your_table_name';
Note: Your_table_name should be replaced by your actual table name
db2 'describe table "SCHEMA_NAME"."TBL_NAME"'
Since all answers are using COUNT()
, you can also use MAX()
to get the number of columns in a specific table as
SELECT MAX(ORDINAL_POSITION) NumberOfColumnsInTable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'YourDatabaseNameHere'
AND
TABLE_SCHEMA = 'YourSchemaNameHere'
AND
TABLE_NAME = 'YourTableNameHere';
It is possible to find the number of columns in a table just by using 3 simple lines of PHP code.
$sql="SELECT * FROM table";
$query=mysqli_query($connect_dude,$sql);
$num=mysqli_num_fields($query);
$num
would return the number of columns
on a given table in this case.
Hopefully,it would help others.
It's working (mysql) :
SELECT TABLE_NAME , count(COLUMN_NAME)
FROM information_schema.columns
GROUP BY TABLE_NAME
SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_CATALOG = 'database_name' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'table_name'
Its been little late but please take it from me...
In the editor(New Query) by select the database object it can be a table too, if we use the Shortcut Key Alt+F1 we will get all the information of the object and I think will solve your problem as well.
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'table_name';
Or use the sys.columns
--SQL 2005
SELECT *
FROM sys.columns
WHERE OBJECT_NAME(object_id) = 'spt_values'
-- returns 6 rows = 6 columns
--SQL 2000
SELECT *
FROM syscolumns
WHERE OBJECT_NAME(id) = 'spt_values'
-- returns 6 rows = 6 columns
SELECT *
FROM dbo.spt_values
-- 6 columns indeed
SELECT TABLE_SCHEMA
, TABLE_NAME
, number = COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_SCHEMA, TABLE_NAME;
This one worked for me.
Here is how you can get a number of table columns using Python 3, sqlite3 and pragma statement:
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE tablename (d1 VARCHAR, d2 VARCHAR)")
cur = con.cursor()
cur.execute("PRAGMA table_info(tablename)")
print(len(cur.fetchall()))
Well I tried Nathan Koop's answer and it didn't work for me. I changed it to the following and it did work:
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_name'
It also didn't work if I put USE 'database_name'
nor WHERE table_catalog = 'database_name' AND table_name' = 'table_name'
. I actually will be happy to know why.
A MySQL answer adapted slightly from the MSDN example for MySqlDataReader.GetValues:
//assumes you've already created a connection, opened it,
//and executed a query to a reader
while(reader.Read())
{
Object[] values = new Object[reader.FieldCount];
int fieldCount = reader.GetValues(values);
Console.WriteLine("\nreader.GetValues retrieved {0} columns.", fieldCount);
for (int i = 0; i < fieldCount; i++)
Console.WriteLine(values[i]);
}
Using MySqlDataReader.FieldCount
will allow you to retrieve the number of columns in the row you've queried.
Using JDBC in Java:
String quer="SELECT * FROM sample2 where 1=2";
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(quer);
ResultSetMetaData rsmd = rs.getMetaData();
int NumOfCol=0;
NumOfCol=rsmd.getColumnCount();
System.out.println("Query Executed!! No of Colm="+NumOfCol);
Can get using following sql statement:
select count(*) Noofcolumns from SYSCOLUMNS where id=(select id from SYSOBJECTS where name='table_name')
SELECT COUNT(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_CATALOG = 'Database name'
AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'table name'
Source: Stackoverflow.com