如何列出数据库中每个表的行数。相当于
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。
你可以试试这个:
SELECT OBJECT_SCHEMA_NAME(ps.object_Id) AS [schemaname],
OBJECT_NAME(ps.object_id) AS [tablename],
row_count AS [rows]
FROM sys.dm_db_partition_stats ps
WHERE OBJECT_SCHEMA_NAME(ps.object_Id) <> 'sys' AND ps.index_id < 2
ORDER BY
OBJECT_SCHEMA_NAME(ps.object_Id),
OBJECT_NAME(ps.object_id)
如果您使用MySQL >4。X你可以用这个:
select TABLE_NAME, TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA="test";
请记住，对于某些存储引擎，TABLE_ROWS只是一个近似值。
如图所示，这将返回正确的计数，而使用元数据表的方法将只返回估计值。
CREATE PROCEDURE ListTableRowCounts
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TableCounts
(
TableName VARCHAR(500),
CountOf INT
)
INSERT #TableCounts
EXEC sp_msForEachTable
'SELECT PARSENAME(''?'', 1),
COUNT(*) FROM ? WITH (NOLOCK)'
SELECT TableName , CountOf
FROM #TableCounts
ORDER BY TableName
DROP TABLE #TableCounts
END
GO
select T.object_id, T.name, I.indid, I.rows
from Sys.tables T
left join Sys.sysindexes I
on (I.id = T.object_id and (indid =1 or indid =0 ))
where T.type='U'
这里indid=1表示群集索引，而indid=0表示堆索引
