blocks|key|1387274|text|SELECT+COUNT(*)
++FROM+INFORMATION_SCHEMA.COLUMNS
+WHERE+table_catalog+=+'database_name'+--+the+database
+++AND+table_name+=+'table_name'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1387275|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT COUNT(*)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_catalog = 'database_name' -- the database
 AND table_name = 'table_name'
</code></pre>

blocks|key|1387277|text|或者使用sys.columns|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1387278|--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|code-block|syntax|javascript|1387279|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Or use the sys.columns

<pre><code>--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
</code></pre>

blocks|key|1387310|text|用于计算表中列数的查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1387311|select+count(*)+from+user_tab_columns+where+table_name+=+'tablename';|code-block|syntax|javascript|1387312|将tablename替换为要返回其总列数的表的名称。|1387313|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Query to count the number of columns in a table:

<pre><code>select count(*) from user_tab_columns where table_name = 'tablename';
</code></pre>

Replace tablename with the name of the table whose total number of columns you want returned.

blocks|key|1602198|text|在Java中使用JDBC：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1602199|++++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="%2BNumOfCol);|code-block|syntax|javascript|1602200|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Using JDBC in Java:
<pre><code> String quer=&quot;SELECT * FROM sample2 where 1=2&quot;;
 
 Statement st=con.createStatement();
 ResultSet rs=st.executeQuery(quer);
 ResultSetMetaData rsmd = rs.getMetaData();
 int NumOfCol=0;
 NumOfCol=rsmd.getColumnCount();
 System.out.println(&quot;Query Executed!! No of Colm=&quot;+NumOfCol);
</code></pre>

blocks|key|1602271|text|下面的查询查找表中的列：-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1602272|+SELECT+COUNT(COLUMN_NAME)+FROM+USER_TAB_COLUMNS
+WHERE+TABLE_NAME+=+'TableName';|code-block|syntax|javascript|1602273|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Following query finds how columns in table:-

<pre><code> SELECT COUNT(COLUMN_NAME) FROM USER_TAB_COLUMNS
 WHERE TABLE_NAME = 'TableName';
</code></pre>

blocks|key|1602306|text|可以使用以下sql语句获取：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1602307|select+count(*)+Noofcolumns+from+SYSCOLUMNS+where+id=(select+id+from+SYSOBJECTS+where+name='table_name')|code-block|syntax|javascript|1602308|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Can get using following sql statement:

<pre><code>select count(*) Noofcolumns from SYSCOLUMNS where id=(select id from SYSOBJECTS where name='table_name')
</code></pre>

blocks|key|1602312|text|SELECT+COUNT(COLUMN_NAME)+FROM+INFORMATION_SCHEMA.COLUMNS+WHERE+
TABLE_CATALOG+=+'database_name'+AND+TABLE_SCHEMA+=+'dbo'
AND+TABLE_NAME+=+'table_name'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1602313|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_CATALOG = 'database_name' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'table_name'
</code></pre>

blocks|key|1602322|text|略微改编自MSDN+example+for+MySqlDataReader.GetValues的MySQL答案|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1602323|//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%2B%2B)
++++++++Console.WriteLine(values[i]);
}|code-block|syntax|javascript|1602324|使用MySqlDataReader.FieldCount将允许您检索所查询行中的列数。|style|CODE|1602325|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getvalues.aspx^0|5|16|0|0|0|2|Q|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|Z|8|@$A|10|B|11|K|L]]|9|@]|C|$]]|$1|M|3|-4|5|6|7|12|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

A MySQL answer adapted slightly from the <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getvalues.aspx" rel="nofollow noreferrer">MSDN example for MySqlDataReader.GetValues</a>:

<pre><code>//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 &lt; fieldCount; i++)
 Console.WriteLine(values[i]);
}
</code></pre>

Using <code>MySqlDataReader.FieldCount</code> will allow you to retrieve the number of columns in the row you've queried.

blocks|key|1387482|text|SELECT+count(*)+FROM+information_schema.`COLUMNS`+C
WHERE+table_name+=+'your_table_name'
AND+TABLE_SCHEMA+=+"your_db_name"|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1387483|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT count(*) FROM information_schema.`COLUMNS` C
WHERE table_name = 'your_table_name'
AND TABLE_SCHEMA = "your_db_name"
</code></pre>

blocks|key|1602350|text|SELECT+COUNT(COLUMN_NAME)+
FROM+INFORMATION_SCHEMA.COLUMNS+
WHERE+
++++TABLE_CATALOG+=+'Database+name'+
++++AND+TABLE_SCHEMA+=+'dbo'+
++++AND+TABLE_NAME+=+'table+name'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1602351|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT COUNT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
 TABLE_CATALOG = 'Database name' 
 AND TABLE_SCHEMA = 'dbo' 
 AND TABLE_NAME = 'table name'
</code></pre>

blocks|key|1387517|text|只需使用3行简单的PHP代码，就可以找到表中的列数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1387518|$sql="SELECT+*+FROM+table";
$query=mysqli_query($connect_dude,$sql);++++
$num=mysqli_num_fields($query);|code-block|syntax|javascript|1387519|在本例中，$num将返回给定表上的columns数。|offset|length|style|CODE|1387520|希望它能帮助其他人。|1387521|entityMap^0|0|0|5|4|H|7|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

It is possible to find the number of columns in a table just by using 3 simple lines of PHP code.

<pre><code>$sql="SELECT * FROM table";
$query=mysqli_query($connect_dude,$sql); 
$num=mysqli_num_fields($query);
</code></pre>

<code>$num</code> would return the number of <code>columns</code> on a given table in this case.

Hopefully,it would help others.

blocks|key|1602394|text|好吧，我试过Nathan+Koop的答案，它对我不起作用。我将其更改为以下代码，它确实起作用了：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1602395|SELECT+COUNT(*)
FROM+INFORMATION_SCHEMA.COLUMNS
WHERE+table_name+=+'table_name'|code-block|syntax|javascript|1602396|如果我把USE+'database_name'和WHERE+table_catalog+=+'database_name'+AND+table_name'+=+'table_name'放在一起，它也不起作用。我真的很乐意知道为什么。|offset|length|style|CODE|1602397|entityMap^0|0|0|4|J|O|1W|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

Well I tried Nathan Koop's answer and it didn't work for me. I changed it to the following and it did work:

<pre><code>SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_name'
</code></pre>

It also didn't work if I put <code>USE 'database_name'</code> nor <code>WHERE table_catalog = 'database_name' AND table_name' = 'table_name'</code>. I actually will be happy to know why.

blocks|key|1602405|text|SELECT+count(*)
FROM+information_schema.columns
WHERE+table_name+=+'Your_table_name';|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1602406|注意:+Your_table_name应该替换为您的实际表名|unstyled|1602407|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT count(*)
FROM information_schema.columns
WHERE table_name = 'Your_table_name';
</code></pre>

Note: Your_table_name should be replaced by your actual table name

blocks|key|1387577|text|SELECT+COUNT(*)+
FROM+INFORMATION_SCHEMA.COLUMNS+
WHERE+
+++TABLE_NAME+=+'table_name';|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1387578|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
 TABLE_NAME = 'table_name';
</code></pre>

blocks|key|1602433|text|SELECT+TABLE_SCHEMA
++++,+TABLE_NAME
++++,+number+=+COUNT(*)+
FROM+INFORMATION_SCHEMA.COLUMNS
GROUP+BY+TABLE_SCHEMA,+TABLE_NAME;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1602434|这个对我很有效。|unstyled|1602435|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT TABLE_SCHEMA
 , TABLE_NAME
 , number = COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_SCHEMA, TABLE_NAME;
</code></pre>

This one worked for me.

blocks|key|1602466|text|下面是如何使用Python3、sqlite3和杂注语句获取多个表列：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1602467|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()))|code-block|syntax|javascript|1602468|Source|offset|length|1602469|entityMap|0|LINK|mutability|MUTABLE|url|https://www.daniweb.com/programming/software-development/threads/124403/sqlite3-how-to-see-column-names-for-table^0|0|0|0|6|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Here is how you can get a number of table columns using Python 3, sqlite3 and pragma statement:

<pre><code>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()))
</code></pre>

<a href="https://www.daniweb.com/programming/software-development/threads/124403/sqlite3-how-to-see-column-names-for-table" rel="nofollow noreferrer">Source</a>

blocks|key|1387629|text|db2+'describe+table+"SCHEMA_NAME"."TBL_NAME"'|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1387630|entityMap^0|0|19|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

<code>db2 'describe table "SCHEMA_NAME"."TBL_NAME"'</code>

blocks|key|1387641|text|它正在工作(mysql)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1387642|SELECT+TABLE_NAME+,+count(COLUMN_NAME)
FROM+information_schema.columns
GROUP+BY+TABLE_NAME+|code-block|syntax|javascript|1387643|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

It's working (mysql) :
<pre><code>SELECT TABLE_NAME , count(COLUMN_NAME)
FROM information_schema.columns
GROUP BY TABLE_NAME 
</code></pre>

I would like to know if it's possible to find the number of both rows and columns within a table.
<pre><code>SELECT COUNT(*)
FROM tablename
</code></pre>

Find the number of columns in a table

MySQL

Java

我想知道是否有可能找到一个表中的行数和列数。SELECT COUNT(*)FROM tablename

问查找表中的列数
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查找表中的列数EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查找表中的列数
EN