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|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|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|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|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|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|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|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|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|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|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>

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|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|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|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|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|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>

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

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

问查找表中的列数
EN

回答 18

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查找表中的列数EN

回答 18

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查找表中的列数
EN