[MSSQL] 16. 용량 큰 테이블 크기, Row 수 구하기

DB에 존재하는 모든 사용자 테이블의 용량과 Row Count를 빠르게 구하는 쿼리입니다. 

어떤 오브젝트가 용량을 많이 차지하고 있는지 빠르게 확인할 수 있습니다.

IF OBJECT_ID(N'tempdb..#DB') IS NOT NULL
BEGIN
	DROP TABLE #DB
END

CREATE TABLE #DB (name sysname, dbid smallint)

INSERT INTO #DB (name, dbid)
SELECT name, dbid FROM master.dbo.sysdatabases
WHERE name not in ('master', 'msdb', 'model')
GO

DECLARE @dbname sysname
DECLARE @min_dbid smallint
DECLARE @SQL varchar(8000)

IF OBJECT_ID(N'tempdb..#TABLESIZE') IS NOT NULL 
BEGIN
     DROP TABLE #TABLESIZE
END

CREATE TABLE #TABLESIZE (
	  database_id INT
	, dbname sysname
	, schname sysname
	, tabname sysname
	, Rows BIGINT
	, [Used_MB] NUMERIC(36, 2)
	, [Unused_MB] NUMERIC(36, 2)
	, [Total_MB] NUMERIC(36, 2)
);

SET @min_dbid = 2

WHILE (@min_dbid IS NOT NULL)
BEGIN
  SELECT @min_dbid = MIN(dbid) FROM #DB WHERE dbid > @min_dbid
  IF (@min_dbid IS NULL)  BREAK

  SELECT @dbname = name FROM #DB WHERE dbid = @min_dbid

  SET @SQL = 'USE [' + @dbname + ']; '
  SET @SQL = @SQL + '
		SELECT 
				DB_ID() AS database_id,
				DB_NAME() AS dbName,
				A.SchemaName, A.TableName, B.Rowcounts, A.Used_MB, A.Unused_MB, A.Total_MB
		FROM (
			SELECT 
					 s.Name AS SchemaName
					,t.Name AS TableName
					,CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB
					,CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB
					,CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
			FROM sys.tables t
			INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
			INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
			INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
			INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
			WHERE t.is_ms_shipped = 0 
			AND t.type = ''U''
			AND i.OBJECT_ID > 255
			AND i.has_filter = 0
			GROUP BY t.Name, s.Name
		) AS A
		INNER JOIN (
			SELECT 	 s.Name AS SchemaName
					,t.Name AS TableName
					,SUM(p.Rows) AS Rowcounts
			FROM sys.tables t
			INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
			INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
			INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
			WHERE t.is_ms_shipped = 0 
			AND t.type = ''U''
			AND i.OBJECT_ID > 255
			AND i.has_filter = 0
			AND i.index_id < 2
			GROUP BY t.Name, s.Name
		) AS B ON A.SchemaName = B.SchemaName AND A.TableName = B.TableName

'
  INSERT INTO #TABLESIZE --(dbname, schname, tabname, Rows, [Used_MB], [Unused_MB], [Total_MB])
  EXEC (@SQL)
END

SELECT 
	  database_id AS DBID
	, dbname
	, schname
	, tabname
	, Rows
	, Used_MB
	, Unused_MB
	, Total_MB
FROM #TABLESIZE 
ORDER BY database_id, rows DESC

IF OBJECT_ID(N'tempdb..#TABLESIZE') IS NOT NULL 
BEGIN
     DROP TABLE #TABLESIZE;
END
GO