[MSSQL] 17. 파티션 테이블 조회 및 Row수 구하기

DB에 존재하는 모든 사용자 파티션 테이블을 조회하고 Row 수를 구할 때 사용하는 쿼리입니다.

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..#PARTITION') IS NOT NULL 
BEGIN
     DROP TABLE #PARTITION
END

CREATE TABLE #PARTITION (
	  database_id int
	, dbName sysname
	, schName sysname
	, tabName sysname
	, PartitionScheme sysname
	, PartitionFunc sysname
	, FileGroupName sysname
	, indexName sysname
	, indexID INT
	, PartitionNumber INT
	, compression_level varchar(100)
	, LowerBoundaryValue sql_variant
	, UpperBoundaryValue sql_variant
	, PartitionFunctionRange varchar(20)
	, Rows BIGINT
);

SET @min_dbid = 4

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,
		OBJECT_SCHEMA_NAME(p.object_id) AS schemaName,
		OBJECT_NAME(p.object_id) AS ObjectName, 
		ds.name AS PartitionScheme, 
		pf.name AS PartitionFunc,
		fg.name AS FileGroupName, 
		i.name AS IndexName, 
		p.index_id AS IndexID, 
		p.partition_number AS PartitionNumber, 
		p.data_compression_desc as compression_level,
		prv_left.value AS LowerBoundaryValue, 
		prv_right.value AS UpperBoundaryValue, 
		CASE pf.boundary_value_on_right WHEN 1 THEN ''RIGHT'' ELSE ''LEFT'' END AS PartitionFunctionRange, 
		p.rows AS Rows
	FROM
		sys.partitions AS p INNER JOIN
		sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN
		sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id INNER JOIN
		sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id INNER JOIN
		sys.partition_functions AS pf ON pf.function_id = ps.function_id INNER JOIN
		sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number INNER JOIN
		sys.filegroups AS fg ON fg.data_space_id = dds2.data_space_id LEFT OUTER JOIN
		sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1 LEFT OUTER JOIN
		sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
	WHERE p.index_id = (select min(index_id) from sys.indexes where object_id = p.object_id)
	order by p.object_id, p.index_id, p.partition_number
	'
  INSERT INTO #PARTITION
  EXEC (@SQL)
END

SELECT 
	  database_id AS DBID
	, dbName 
	, schName 
	, tabName 
	, PartitionScheme AS PartScheme
	, PartitionFunc AS PartFunc
	, FileGroupName AS fgName
	--, indexName 
	--, indexID 
	, PartitionNumber AS PartNum
	, compression_level AS CompressLevel
	, LowerBoundaryValue AS LValue
	, UpperBoundaryValue AS RValue
	, PartitionFunctionRange AS PRange
	, Rows
FROM #PARTITION 
ORDER BY dbName, tabName, PartitionNumber