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
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'