如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案，但其他方法是受欢迎的
当前回答
如图所示，这将返回正确的计数，而使用元数据表的方法将只返回估计值。
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 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)
要在SQL Management Studio中获取该信息，右键单击数据库，然后选择Reports—> Standard Reports—> Disk Usage by Table。
如图所示，这将返回正确的计数，而使用元数据表的方法将只返回估计值。
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.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;
sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX);
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX))
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'
输出:
