SQLserver查询数据库所用表的空间使用量

最新推荐文章于 2024-05-29 14:20:04 发布

SmallManBaby

最新推荐文章于 2024-05-29 14:20:04 发布

阅读量848

点赞数

文章标签： SQLSERVER 表使用量使用空间表的大小

本文链接：https://blog.csdn.net/guoyongshengjsx/article/details/17298863

版权

CREATE VIEW vDataDestribute
AS
	SELECT TOP 1000
	a3.name AS [schemaname],
	a2.name AS [tablename],
	a1.rows as row_count,
	(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [reserved(K)],
	a1.data * 8 AS [data(k)],
	(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(k)],
	(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(k)],
	a1.data * 8*1024/(CASE WHEN a1.Rows=0 THEN 1 ELSE a1.Rows END) BytesPerRow
	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 [reserved(K)] DESC
GO

SELECT * FROM vDataDestribute order by row_count desc   '最后查询出来的结果