1 스크립트

2 시스템 sp로 등록하기

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

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'