如何列出数据库中每个表的行数。相当于

select count(*) from table1
select count(*) from table2
...
select count(*) from tableN

我会张贴一个解决方案,但其他方法是受欢迎的


当前回答

如果您使用MySQL >4。X你可以用这个:

select TABLE_NAME, TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA="test";

请记住,对于某些存储引擎,TABLE_ROWS只是一个近似值。

其他回答

要在SQL Management Studio中获取该信息,右键单击数据库,然后选择Reports—> Standard Reports—> Disk Usage by Table。

我在http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21021上找到了一个帮助我的片段:

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1)
order by TableName;
SELECT 
    T.NAME AS 'TABLE NAME',
    P.[ROWS] AS 'NO OF ROWS'
FROM SYS.TABLES T 
INNER JOIN  SYS.PARTITIONS P ON T.OBJECT_ID=P.OBJECT_ID;

以下是我对这个问题的看法。它包含所有模式,只列出带行的表。YMMV

select distinct schema_name(t.schema_id) as schema_name, t.name as 
table_name, p.[Rows]
from sys.tables as t
INNER JOIN sys.indexes as i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = 
p.index_id
where p.[Rows] > 0
order by schema_name;

如果您使用MySQL >4。X你可以用这个:

select TABLE_NAME, TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA="test";

请记住,对于某些存储引擎,TABLE_ROWS只是一个近似值。