SELECT OBJECT_NAME(id) AS [TBName],MAX(rowcnt)[rowcounts] FROM sys.sysindexesGROUP BY OBJECT_NAME(id)
尝试后,我通过select SUM(rowcounts) from (SELECT OBJECT_NAME(id) AS [TBName],MAX(rowcnt)[rowcounts] FROM sys.sysindexes GROUP BY OBJECT_NAME(id))ss获取到一个数据,心中有一些疑问,然后去百度了一下,貌似这样子得到的数据是粗略的,因为“在MS Sql数据库中,每个数据表都在sys.sysindexes系统表中拥有至少一条记录,记录中的rows 或rowcnt字段会定时记录表的记录总数。注意是定时,这说明了用这个方法得到的总记录数不是一个精确值,原因是MsSQL并不是实时更新该字段的值,而是定时更新”,mssql有那么多隐藏的东西,真不知道如何系统的去学!但是不管如何,现在已经解开先前的一些疑惑了。如果接下来的几天,没人回答的话,我就结贴了。谢谢各位的指点!
SELECT OBJECT_SCHEMA_NAME(ddps.object_id) + '.' + OBJECT_NAME(ddps.object_id) AS name , SUM(ddps.row_count) AS row_count FROM sys.dm_db_partition_stats AS ddps JOIN sys.indexes ON indexes.object_id = ddps.object_id AND indexes.index_id = ddps.index_id WHERE indexes.type_desc IN ( 'CLUSTERED', 'HEAP' ) AND OBJECTPROPERTY(ddps.object_id, 'IsMSShipped') = 0 GROUP BY ddps.object_id
我把这改为select sum (row_count)from (SELECT OBJECT_SCHEMA_NAME(ddps.object_id) + '.' + OBJECT_NAME(ddps.object_id) AS name , SUM(ddps.row_count) AS row_count FROM sys.dm_db_partition_stats AS ddps JOIN sys.indexes as t ON t.object_id = ddps.object_id AND t.index_id = ddps.index_id WHERE t.type_desc IN ( 'CLUSTERED', 'HEAP' ) AND OBJECTPROPERTY(ddps.object_id, 'IsMSShipped') = 0 GROUP BY ddps.object_id) ss 结果是51683,用你在7楼的代码结果是279579,为什么差别那么大?
@command2="sp_spaceused '?'",
@command3= "SELECT count(*) FROM ? "
update #tb_count set tb_no=tb_no+@t这里是不是重复累加了?
set @sql='select @no=count(*) from '+@tb_name
exec sp_executesql @sql,N'@no int',@no output
from (SELECT OBJECT_NAME(id) AS [TBName],MAX(rowcnt)[rowcounts]
FROM sys.sysindexes GROUP BY OBJECT_NAME(id))ss获取到一个数据,心中有一些疑问,然后去百度了一下,貌似这样子得到的数据是粗略的,因为“在MS Sql数据库中,每个数据表都在sys.sysindexes系统表中拥有至少一条记录,记录中的rows 或rowcnt字段会定时记录表的记录总数。注意是定时,这说明了用这个方法得到的总记录数不是一个精确值,原因是MsSQL并不是实时更新该字段的值,而是定时更新”,mssql有那么多隐藏的东西,真不知道如何系统的去学!但是不管如何,现在已经解开先前的一些疑惑了。如果接下来的几天,没人回答的话,我就结贴了。谢谢各位的指点!
SUM(ddps.row_count) AS row_count
FROM sys.dm_db_partition_stats AS ddps
JOIN sys.indexes ON indexes.object_id = ddps.object_id
AND indexes.index_id = ddps.index_id
WHERE indexes.type_desc IN ( 'CLUSTERED', 'HEAP' )
AND OBJECTPROPERTY(ddps.object_id, 'IsMSShipped') = 0
GROUP BY ddps.object_id
SUM(ddps.row_count) AS row_count
FROM sys.dm_db_partition_stats AS ddps
JOIN sys.indexes as t ON t.object_id = ddps.object_id
AND t.index_id = ddps.index_id
WHERE t.type_desc IN ( 'CLUSTERED', 'HEAP' )
AND OBJECTPROPERTY(ddps.object_id, 'IsMSShipped') = 0
GROUP BY ddps.object_id) ss 结果是51683,用你在7楼的代码结果是279579,为什么差别那么大?