获取数据表的磁盘占用大小和行数

[sql]代码库

BEGIN TRY
	SELECT a3.NAME AS [schemaname]
		,a2.NAME AS [tablename]
		,a1.rows AS row_count
		,(a1.reserved + ISNULL(a4.reserved, 0)) * 8 AS reserved
		,a1.data * 8 AS data
		,(
			CASE 
				WHEN (a1.used + ISNULL(a4.used, 0)) > a1.data
					THEN (a1.used + ISNULL(a4.used, 0)) - a1.data
				ELSE 0
				END
			) * 8 AS index_size
		,(
			CASE 
				WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
					THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
				ELSE 0
				END
			) * 8 AS unused
		,CASE 
			WHEN a1.data = 0
				THEN 0
			ELSE (
					CASE 
						WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
							THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
						ELSE 0
						END
					) * 10000 / (a1.reserved + ISNULL(a4.reserved, 0))
			END unusedRate
	FROM (
		SELECT ps.object_id
			,SUM(CASE 
					WHEN (ps.index_id < 2)
						THEN row_count
					ELSE 0
					END) AS [rows]
			,SUM(ps.reserved_page_count) AS reserved
			,SUM(CASE 
					WHEN (ps.index_id < 2)
						THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
					ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
					END) AS data
			,SUM(ps.used_page_count) AS used
		FROM sys.dm_db_partition_stats ps
		GROUP BY ps.object_id
		) AS a1
	LEFT OUTER JOIN (
		SELECT it.parent_id
			,SUM(ps.reserved_page_count) AS reserved
			,SUM(ps.used_page_count) AS used
		FROM sys.dm_db_partition_stats ps
		INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
		WHERE it.internal_type IN (
				202
				,204
				)
		GROUP BY it.parent_id
		) AS a4 ON (a4.parent_id = a1.object_id)
	INNER JOIN sys.all_objects a2 ON (a1.object_id = a2.object_id)
	INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
	WHERE a2.type <> N'S'
		AND a2.type <> N'IT'
	ORDER BY unusedRate DESC
END TRY
 
BEGIN CATCH
	SELECT - 100 AS l1
		,1 AS schemaname
		,ERROR_NUMBER() AS tablename
		,ERROR_SEVERITY() AS row_count
		,ERROR_STATE() AS reserved
		,ERROR_MESSAGE() AS data
		,1 AS index_size
		,1 AS unused
END CATCH