计算数据库中各个表的数据量和每行记录所占用空间代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】计算数据库中各个表的数据量和每行记录所占用空间

作者:艳青 / 发布于2014/11/4/ 944


	CREATE TABLE #tablespaceinfo
	 (
	 nameinfo VARCHAR(500) ,
	 rowsinfo BIGINT ,
	 reserved VARCHAR(20) ,
	 datainfo VARCHAR(20) ,
	 index_size VARCHAR(20) ,
	 unused VARCHAR(20)
	 )
	
	DECLARE @tablename VARCHAR(255);
	
	DECLARE Info_cursor CURSOR
	FOR
	 SELECT '[' + [name] + ']'
	 FROM sys.tables
	 WHERE type = 'U';
	
	OPEN Info_cursor
	FETCH NEXT FROM Info_cursor INTO @tablename
	
	WHILE @@FETCH_STATUS = 0
	 BEGIN
	 INSERT INTO #tablespaceinfo
	 EXEC sp_spaceused @tablename
	 FETCH NEXT FROM Info_cursor
	 INTO @tablename
	 END
	
	CLOSE Info_cursor
	DEALLOCATE Info_cursor
	
	--创建临时表
	CREATE TABLE [#tmptb]
	 (
	 TableName VARCHAR(50) ,
	 DataInfo BIGINT ,
	 RowsInfo BIGINT ,
	 Spaceperrow AS ( CASE RowsInfo
	 WHEN 0 THEN 0
	 ELSE DataInfo / RowsInfo
	 END ) PERSISTED
	 )

	--插入数据到临时表
	INSERT INTO [#tmptb]
	 ( [TableName] ,
	 [DataInfo] ,
	 [RowsInfo]
	 )
	 SELECT [nameinfo] ,
	 CAST(REPLACE([datainfo], 'KB', '') AS BIGINT) AS 'datainfo' ,
	 [rowsinfo]
	 FROM #tablespaceinfo
	 ORDER BY CAST(REPLACE(reserved, 'KB', '') AS INT) DESC

	
	--汇总记录
	SELECT [tbspinfo].* ,
	 [tmptb].[Spaceperrow] AS '每行记录大概占用空间（KB）'
	FROM [#tablespaceinfo] AS tbspinfo ,
	 [#tmptb] AS tmptb
	WHERE [tbspinfo].[nameinfo] = [tmptb].[TableName]
	ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS INT) DESC

	DROP TABLE [#tablespaceinfo]
	DROP TABLE [#tmptb]

试试其它关键字

　数据量　　占用空间　

同语言下

可能有用的

艳青贡献的其它代码(8)