如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案,但其他方法是受欢迎的
如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案,但其他方法是受欢迎的
当前回答
以下是我对这个问题的看法。它包含所有模式,只列出带行的表。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;
其他回答
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;
幸运的是,SQL Server管理工作室会给你一个提示,告诉你如何做到这一点。 这样做,
启动SQL Server跟踪并打开您正在进行的活动(过滤器) 如果您不是一个人,请通过您的登录ID,并设置应用程序名称 到Microsoft SQL Server Management Studio),暂停跟踪并丢弃迄今为止记录的任何结果; 然后,右键单击一个表,并从弹出菜单中选择属性; 再次启动跟踪; 现在在SQL Server Management studio中选择左侧的存储属性项;
暂停跟踪,看看microsoft生成了什么TSQL。
在可能是最后一个查询中,您将看到以exec sp_executesql N'SELECT开头的语句
当您将执行的代码复制到visual studio时,您将注意到这段代码生成了微软工程师用于填充属性窗口的所有数据。
当你对该查询进行适度修改时,你会得到这样的结果:
SELECT
SCHEMA_NAME(tbl.schema_id)+'.'+tbl.name as [table], --> something I added
p.partition_number AS [PartitionNumber],
prv.value AS [RightBoundaryValue],
fg.name AS [FileGroupName],
CAST(pf.boundary_value_on_right AS int) AS [RangeType],
CAST(p.rows AS float) AS [RowCount],
p.data_compression AS [DataCompression]
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) AND p.index_id=idx.index_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = idx.data_space_id and dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_schemes AS ps ON ps.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON prv.boundary_id = p.partition_number and prv.function_id = ps.function_id
LEFT OUTER JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id or fg.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON pf.function_id = prv.function_id
现在的查询并不完美,你可以更新它来满足你可能遇到的其他问题,关键是,你可以使用微软的知识来得到你的大多数问题,通过执行你感兴趣的数据和跟踪使用分析器生成的TSQL。
我喜欢认为MS工程师知道SQL server是如何工作的,它会生成TSQL,适用于所有项目,你可以使用你正在使用的SSMS上的版本,所以它在以前、现在和未来的各种版本上都很好。
记住,不要只是照抄,要试着理解它,否则你可能会得到错误的答案。
沃尔特
查找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
接受的答案在Azure SQL上不适合我,这里有一个,它非常快,完全符合我的要求:
select t.name, s.row_count
from sys.tables t
join sys.dm_db_partition_stats s
ON t.object_id = s.object_id
and t.type_desc = 'USER_TABLE'
and t.name not like '%dss%'
and s.index_id = 1
order by s.row_count 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/