SQL 计算表中的行数

我需要向数据库发送一个 SQL 查询，它告诉我一个表中有多少行。我可以使用 SELECT 获取表中的所有行，然后对它们进行计数，但我不喜欢这样做。是否有其他方法来询问表中的行数到 SQL 服务器？

301061 次浏览
小开
最佳答案

Yes, SELECT COUNT(*) FROM TableName

小开 
select sum([rows])
from sys.partitions
where object_id=object_id('tablename')
and index_id in (0,1)

is very fast but very rarely inaccurate.

小开

Use This Query :

Select
S.name + '.' + T.name As TableName ,
SUM( P.rows ) As RowCont


From sys.tables As T
Inner Join sys.partitions As P On ( P.OBJECT_ID = T.OBJECT_ID )
Inner Join sys.schemas As S On ( T.schema_id = S.schema_id )
Where
( T.is_ms_shipped = 0 )
AND
( P.index_id IN (1,0) )
And
( T.type = 'U' )


Group By S.name , T.name


Order By SUM( P.rows ) Desc
小开

Here is the Query

select count(*) from tablename

or

select count(rownum) from studennt
小开

Why don't you just right click on the table and then properties -> Storage and it would tell you the row count. You can use the below for row count in a view. Use your table's name in the WHERE clause.

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('TableName')
AND (index_id=0 or index_id=1)`
小开

The index statistics likely need to be current, but this will return the number of rows for all tables that are not MS_SHIPPED.

select o.name, i.rowcnt
from sys.objects o join sys.sysindexes i
on o.object_id = i.id
where o.is_ms_shipped = 0
and i.rowcnt > 0
order by o.name

提交答案