SELECT OBJECT_NAME(object_id) AS Object_Name, i.name AS Index_Name, p.rows AS Table_Rows FROM sys.partitions p LEFT JOIN sys.sysindexes i ON p.object_id = i.id AND p.index_id = i.indid WHERE object_id = OBJECT_ID(\’TableName\’)
那么我们还有一些疑问，我们先来看看这些问题吧！
1：没有索引的表是否也可以使用上面脚本？
2：只有非聚集索引的堆表是否可以使用上面脚本？
3：有多个索引的表，是否记录数会存在不一致的情况？
4：统计信息不准确的表，是否rows也会不准确
5: 分区表的情况又是怎么样？
6：对象目录视图sys.partitions与sp_spaceused获取的表记录函数是否准确。
如下所示，我们先构造测试案例：
IF EXISTS(SELECT 1 FROM sys.objects WHERE type=\’U\’ AND name=\’TEST_TAB_ROW\’) BEGIN DROP TABLE TEST_TAB_ROW; END IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE type=\’U\’ AND name=\’TEST_TAB_ROW\’) BEGIN CREATE TABLE TEST_TAB_ROW ( ID INT, NAME CHAR(200) ) END GO SET NOCOUNT ON; BEGIN TRAN DECLARE @Index INT =1; WHILE @Index <= 100000 BEGIN INSERT INTO TEST_TAB_ROW VALUES(@Index, NEWID()); SET @Index+=1; IF (@Index % 5000) = 0 BEGIN IF @@TRANCOUNT > 0 BEGIN COMMIT; BEGIN TRAN END END END IF @@TRANCOUNT > 0 BEGIN COMMIT; END GO
关于问题1、问题2，都可以使用上面脚本， 如下测试所示：
SELECT OBJECT_NAME(object_id) AS Object_Name, i.name AS Index_Name, p.rows AS Table_Rows FROM sys.partitions p LEFT JOIN sys.sysindexes i ON p.object_id = i.id AND p.index_id = i.indid WHERE object_id = OBJECT_ID(\’dbo.TEST_TAB_ROW\’)
SELECT OWNER , TABLE_NAME, NUM_ROWS , LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = \’&OWNER\’ AND TABLE_NAME = \’&TABLE_NAME\’; SELECT OWNER, TABLE_NAME, NUM_ROWS , LAST_ANALYZED FROM ALL_TABLES WHERE OWNER =\’&OWNER\’ AND TABLE_NAME=\’&TABLE_NAME\’; SELECT TABLE_NAME, NUM_ROWS , LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME=\’&TABLE_NAME\’
