27,579
社区成员
发帖
与我相关
我的任务
分享
Exec sp_MSforeachtable@precommand="Createtable
##(name sysname,rowsint,reservednvarchar(20),datanvarchar(20),index_sizenvarchar(20),unusednvarchar(20))",@command1="insertinto
##exec sp_spaceused'?'",@postcommand="select*from
##"droptable
##
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(COL VARCHAR(50))
INSERT INTO TB
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO
BEGIN TRAN
INSERT INTO TB
SELECT 'D' UNION ALL
SELECT 'E'
--ROLLBACK TRAN
SELECT * FROM TB WITH(NOLOCK)
exec SP_SPACEUSED 'TB'
SELECT ROWS
FROM SYS.PARTITIONS
where OBJECT_NAME(OBJECT_ID)='TB'
SELECT OBJECT_NAME(OBJECT_ID),ROWS
FROM SYS.PARTITIONS
ORDER BY OBJECT_NAME(OBJECT_ID)
SELECT
[rowCount] = SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
WHERE object_id = object_id('tb');
SELECT OBJECT_NAME(OBJECT_ID),OBJECT_ID,ROWS,*
FROM SYS.PARTITIONS
ORDER BY OBJECT_NAME(OBJECT_ID)
很多表有几条相同的记录
分区表就有n+m条
嗯,取最大的,应该是需要的:
SELECT OBJECT_NAME(OBJECT_ID),max(ROWS)
FROM SYS.PARTITIONS
group BY OBJECT_NAME(OBJECT_ID)