本文介绍如何在SQL Server中查询所有用户表及其行数，包括针对不同版本的SQL Server提供兼容的查询语句。文章提供了两种查询方法，一种适用于较旧版本，另一种则针对SQL Server 2005及以后版本。
-- Shows all user tables and row counts for the
current database
-- Remove OBJECTPROPERTY function call to include
system objects
SELECT
o.NAME,
i.rowcnt
FROM sysindexes AS
i
INNER JOIN
sysobjects AS o ON
i.id =
o.id
WHERE
i.indid < 2
AND
OBJECTPROPERTY(o.id,
'IsMSShipped') = 0
ORDER BY
o.NAME
注意:
sysindexes这个系统表会在将来的SQL Server中删除,所以建议SQL
2005和2008使用下面的DMV代替:
-- Shows all user tables and row counts for the
current database
-- Remove is_ms_shipped = 0 check to include system
objects
-- i.index_id < 2 indicates clustered index (1)
or hash table (0)
SELECT
o.name,
ddps.row_count
FROM sys.indexes
AS i
INNER JOIN
sys.objects AS o ON
i.OBJECT_ID =
o.OBJECT_ID
INNER JOIN
sys.dm_db_partition_stats AS
ddps ON
i.OBJECT_ID =
ddps.OBJECT_ID
AND
i.index_id =
ddps.index_id
WHERE
i.index_id < 2
AND
o.is_ms_shipped = 0
ORDER BY
o.NAME
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
