如何列出数据库中每个表的行数。相当于
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。
如果您使用MySQL >4。X你可以用这个:
select TABLE_NAME, TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA="test";
请记住，对于某些存储引擎，TABLE_ROWS只是一个近似值。
如果你使用的是SQL Server 2005及以上版本，你也可以使用这个:
SELECT
t.NAME AS TableName,
i.name as indexName,
p.[Rows],
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes 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
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY
object_name(i.object_id)
在我看来，它比sp_msforeachtable输出更容易处理。
查找SQL reference (http://www.codeproject.com/Tips/811017/Fastest-way-to-find-row-count-of-all-tables-in-SQL)中所有表的行数的最快方法
SELECT T.name AS [TABLE NAME], I.rows AS [ROWCOUNT]
FROM sys.tables AS T
INNER JOIN sys.sysindexes AS I ON T.object_id = I.id
AND I.indid < 2
ORDER BY I.rows DESC
我想分享对我有效的方法
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sdmvPTNS.row_count) AS [RowCount]
FROM
sys.objects AS sOBJ
INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
ON sOBJ.object_id = sdmvPTNS.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND sdmvPTNS.index_id < 2
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY [TableName]
GO
数据库托管在Azure中，最终结果是:
来源:https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/
