#title 테이블의 인덱스 및 파티션 정보보기
[[TableOfContents]]

--https://www.sqlservercentral.com/forums/topic/there-and-back-again-from-a-partition-number-to-a-filegroup-and-vice-versa
{{{
        select  p.[object_id]
              , OBJECT_SCHEMA_NAME(p.[object_id]) AS TbSchemaName
              , OBJECT_NAME(p.[object_id]) AS TbName
              , p.index_id
              , p.partition_number
              , p.rows
              , index_name = i.[name]
              , index_type_desc = i.type_desc
              , i.data_space_id
              , ds1.NAME AS [FILEGROUP_NAME]
              , pf.function_id
              , pf.[name] AS Pf_Name
              , pf.type_desc
              , pf.boundary_value_on_right
              , destination_data_space_id = dds.destination_id
              , prv.parameter_id
              , prv.value
        from    sys.partitions p
        inner join sys.indexes i
                on p.[object_id] = i.[object_id]
                   and p.index_id = i.index_id
        inner JOIN sys.data_spaces ds
                on i.data_space_id = ds.data_space_id
        inner JOIN sys.partition_schemes ps
                on ds.data_space_id = ps.data_space_id
        inner JOIN sys.partition_functions pf
                on ps.function_id = pf.function_id
        inner join sys.destination_data_spaces dds
                on dds.partition_scheme_id = ds.data_space_id
                   and p.partition_number = dds.destination_id
        INNER JOIN sys.data_spaces ds1
                on ds1.data_space_id = dds.data_space_id
        left outer JOIN sys.partition_range_values prv
                on prv.function_id = ps.function_id
                   and p.partition_number = prv.boundary_id
         where p.object_id = object_id('AccountAbilityUnlock')
        order by TbName
              , TbSchemaName
              , index_type_desc
              , index_name
              , p.partition_number ;
}}}


==== 스크립트 ====
{{{
declare 
	@is_partitioned bit
,	@table_name nvarchar(500)
,	@schema_name nvarchar(500);

set @schema_name = '스키마명';
set @table_name = '테이블명';

with index_keys(id, indid, column_name, keyno)
as
(
	select 
		id
	,	indid 
	,	col_name(id, colid) +
		case 
			when indexkey_property(id, colid, keyno, 'IsDescending') = 1 then '(-)' 
			else '(+)'
		end column_name
	,	keyno
	from sys.sysindexkeys
),
sys_index_key(id, indid, column_name)
as
(
	select distinct
		id
	,	indid
	,	stuff((
		select ',' + column_name as 'text()'
		from index_keys 
		where a.indid = indid
		and a.id = id
		for xml path('')
		), 1, 1, '') column_name
	from index_keys a
) 
select
	(select top 1 schema_name(schema_id) from sys.all_objects 
	where a.id = object_id) sch_name
,	object_name(b.id) table_name
,	b.name index_name
,	a.column_name index_key
,	b.rowcnt row_count
,	case when b.indid = 1 then 'clustered' else 'nonclustered' end index_type
,	(select top 1 groupname from sys.sysfilegroups where b.groupid = groupid) group_name
,	isnull((select top 1 1 from sys.partitions 
	where a.id = object_id and a.indid = index_id
	and partition_number > 1), 0) is_partitoned
,	convert(varchar, convert(numeric(36,2), b.reserved / 1024.00)) + ' MB' reserved
,	convert(varchar, convert(numeric(36,2), b.used / 1024.00)) + ' MB' used
,	b.OrigFillFactor fill_factor
from sys_index_key a
	inner join sys.sysindexes b
		on a.id = b.id
		and a.indid = b.indid
	inner join sys.all_objects c
		on a.id = c.object_id
where 1=1
and b.name not like '_WA_Sys%'
and object_name(b.id) = @table_name
order by 1, 2


if exists (select * from sys.partitions where object_name(object_id) = @table_name)
begin
	;with part
	as
	(
	select 
		a.function_id
	,	a.type_desc
	,	b.boundary_id partition_number
	,	b.value
	,	c.name 
	from sys.partition_functions a 
		inner join sys.partition_range_values b
			on a.function_id = b.function_id
		inner join sys.partition_schemes c
			on a.function_id = c.function_id
	)
	select distinct 
		schema_name(d.schema_id) sch_name
	,	object_name(a.object_id) obj_name
	,	b.name index_name
	,	c.name 
	,	a.rows
	,	e.value boundary_value
    ,   a.data_compression_desc
	from sys.partitions a 
		inner join sys.indexes b
			on a.object_id = b.object_id
			and a.index_id = b.index_id
		inner join sys.data_spaces c
			on b.data_space_id = c.data_space_id
		inner join sys.all_objects d
			on d.object_id = a.object_id
		inner join part e
			on c.name = e.name
			and a.partition_number = e.partition_number
	where object_name(d.object_id)=@table_name
	and schema_name(d.schema_id) = @schema_name
	order by 1,2,3,4,6
end
}}}


==== 시스템 sp로 등록하기 ====
ssms에서 키보드의 '쿼리바로가기'에 등록해서 써라..
{{{
use master
go

create proc sp_helpindex2
    @table_name nvarchar(500) --테이블명
as
declare 
	@is_partitioned bit
--,	@table_name nvarchar(500)
,	@schema_name nvarchar(500);

--set @schema_name = '스키마명';
--set @table_name = '테이블명';

with index_keys(id, indid, column_name, keyno)
as
(
	select 
		id
	,	indid 
	,	col_name(id, colid) +
		case 
			when indexkey_property(id, colid, keyno, 'IsDescending') = 1 then '(-)' 
			else '(+)'
		end column_name
	,	keyno
	from sys.sysindexkeys
),
sys_index_key(id, indid, column_name)
as
(
	select distinct
		id
	,	indid
	,	stuff((
		select ',' + column_name as 'text()'
		from index_keys 
		where a.indid = indid
		and a.id = id
		for xml path('')
		), 1, 1, '') column_name
	from index_keys a
) 
select
	(select top 1 schema_name(schema_id) from sys.all_objects 
	where a.id = object_id) sch_name
,	object_name(b.id) table_name
,	b.name index_name
,	a.column_name index_key
,	b.rowcnt row_count
,	case when b.indid = 1 then 'clustered' else 'nonclustered' end index_type
,	(select top 1 groupname from sys.sysfilegroups where b.groupid = groupid) group_name
,	isnull((select top 1 1 from sys.partitions 
	where a.id = object_id and a.indid = index_id
	and partition_number > 1), 0) is_partitoned
,	convert(varchar, convert(numeric(36,2), b.reserved / 1024.00)) + ' MB' reserved
,	convert(varchar, convert(numeric(36,2), b.used / 1024.00)) + ' MB' used
,	b.OrigFillFactor fill_factor
from sys_index_key a
	inner join sys.sysindexes b
		on a.id = b.id
		and a.indid = b.indid
	inner join sys.all_objects c
		on a.id = c.object_id
where 1=1
and b.name not like '_WA_Sys%'
and object_name(b.id) = @table_name
order by 1, 2


if exists (select * from sys.partitions where object_name(object_id) = @table_name)
begin
	;with part
	as
	(
	select 
		a.function_id
	,	a.type_desc
	,	b.boundary_id partition_number
	,	b.value
	,	c.name 
	from sys.partition_functions a 
		inner join sys.partition_range_values b
			on a.function_id = b.function_id
		inner join sys.partition_schemes c
			on a.function_id = c.function_id
	)
	select distinct 
		schema_name(d.schema_id) sch_name
	,	object_name(a.object_id) obj_name
	,	b.name index_name
	,	c.name 
	,	a.rows
	,	e.value boundary_value
    ,   a.data_compression_desc
	from sys.partitions a 
		inner join sys.indexes b
			on a.object_id = b.object_id
			and a.index_id = b.index_id
		inner join sys.data_spaces c
			on b.data_space_id = c.data_space_id
		inner join sys.all_objects d
			on d.object_id = a.object_id
		inner join part e
			on c.name = e.name
			and a.partition_number = e.partition_number
	where object_name(d.object_id)=@table_name
	--and schema_name(d.schema_id) <> @schema_name
	order by 1,2,3,4,6
end
go

exec sp_MS_marksystemobject 'sp_helpindex2'
}}}