SQL Server 파티션(PARTITION) 정보 확인

해당 DB에서 실행

 

	SELECT	DB_NAME() AS [DB_NAME]
		,	T1.[name] AS TABLE_NAME
		,	T1.[object_id]
		,	T2.[name] AS INDEX_NAME
		,	T4.[name]	AS PARTITION_SCHEME_NAME
		,	T5.[name]	AS PARTITION_FUNCTION_NAME
		,	T3.partition_number AS PARTITION_NUMBER
		,	T3.[rows]	AS PARTITION_ROWS
		,	CASE T5.boundary_value_on_right 
				WHEN 0 THEN 'LEFT' 
				WHEN 1 THEN 'RIGHT' 
				ELSE '' 
			END AS PARTITON_RANGE
		,	T9.[value]	AS RANGE_VALUE		
		,	T7.[name]	AS FILEGROUPS_NAME
		,	T8.[name]	AS [FILE_NAME] 
		,	T8.physical_name	AS FILE_PHYSICAL_NAME
	FROM	sys.tables	AS T1 WITH(NOLOCK)
			INNER JOIN sys.indexes	AS T2 WITH(NOLOCK)
				ON	T2.object_id = T1.object_id
			INNER JOIN sys.partitions	AS T3 WITH(NOLOCK)
				ON	T3.object_id = T2.object_id
				AND	T3.index_id = T2.index_id
			INNER JOIN sys.partition_schemes	AS T4 WITH(NOLOCK)
				ON	T4.data_space_id = T2.data_space_id
			INNER JOIN sys.partition_functions	AS T5 WITH(NOLOCK)
				ON	T5.function_id = T4.function_id
			INNER JOIN sys.destination_data_spaces	AS T6 WITH(NOLOCK)
				ON	T6.partition_scheme_id = T4.data_space_id
				AND	T6.destination_id = T3.partition_number
			INNER JOIN sys.filegroups	AS T7 WITH(NOLOCK)
				ON	T7.data_space_id = T6.data_space_id
			INNER JOIN sys.database_files	AS T8 WITH(NOLOCK)
				ON	T8.data_space_id = T7.data_space_id
			INNER JOIN sys.partition_range_values	AS T9 WITH(NOLOCK)
				ON	T9.function_id = T5.function_id
				AND	T9.boundary_id = T3.partition_number
	WHERE	T1.name = N'테이블이름'	
	ORDER BY
			T2.index_id
		,	T3.partition_number
	;