USE master
GO
IF OBJECT_ID('sp_index') IS NULL EXEC ('CREATE PROCEDURE sp_index AS SELECT 1 AS ID')
GO
ALTER PROCEDURE sp_index @ObjectName SYSNAME
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
WITH IndexColumns
AS (SELECT o.OBJECT_ID AS ObjectId,
CASE o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN 'U' THEN 'TABLE'
WHEN 'V' THEN 'VIEW'
ELSE o.type COLLATE SQL_Latin1_General_CP1_CI_AS
END AS ObjectType,
s.name AS SchemaName,
o.name ObjectName,
CASE WHEN ix.is_primary_key = 1
OR ix.is_unique_constraint = 1 THEN ix.name
ELSE ix.name
END AS IndexName,
ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
CASE ds.type
WHEN 'FG' THEN 'FILEGROUP'
WHEN 'PS' THEN 'PARTITION SCHEME'
WHEN 'FD' THEN 'FILESTREAM'
ELSE ds.TYPE
END AS DataspaceType,
ds.name AS Dataspace,
ix.index_id,
ix.is_unique,
ix.is_primary_key,
ix.IGNORE_DUP_KEY,
ix.is_unique_constraint,
ix.is_disabled,
ix.filter_definition,
ix.fill_factor,
ix.is_padded,
ix.ALLOW_ROW_LOCKS,
ix.ALLOW_PAGE_LOCKS,
pr.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DATA_COMPRESSION,
KeyCnt,
IncludeCnt,
KeyColumns,
IncludeColumns,
PartitionedCol,
CASE WHEN ds.type = 'FG' THEN pr.ROWS
END AS [Rows]
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
INNER JOIN sys.indexes AS ix (NOLOCK) ON o.OBJECT_ID = ix.OBJECT_ID
INNER JOIN sys.data_spaces AS ds (NOLOCK) ON ds.data_space_id = ix.data_space_id
INNER JOIN sys.partitions AS pr (NOLOCK) ON pr.OBJECT_ID = ix.OBJECT_ID
AND pr.index_id = ix.index_id
CROSS APPLY (SELECT SUM(CASE WHEN key_ordinal > 0 THEN 1
ELSE 0
END) AS KeyCnt,
SUM(CASE WHEN is_included_column > 0 THEN 1
ELSE 0
END) AS IncludeCnt,
Util.dbo.StringConcat(CASE WHEN key_ordinal > 0 THEN QUOTENAME(ColumnName) + CASE WHEN is_descending_key = 1 THEN ' DESC'
ELSE ''
END
END, ', ') AS KeyColumns,
Util.dbo.StringConcat(CASE WHEN is_included_column = 1 THEN QUOTENAME(ColumnName)
END, ', ') AS IncludeColumns,
MAX(CASE WHEN partition_ordinal = 1 THEN QUOTENAME(ColumnName)
END) AS PartitionedCol
FROM (SELECT TOP 999999
cl.name AS ColumnName,
ic.key_ordinal,
ic.is_included_column,
ic.partition_ordinal,
ic.is_descending_key
FROM sys.index_columns AS ic (NOLOCK)
INNER JOIN sys.columns AS cl (NOLOCK) ON cl.OBJECT_ID = ic.OBJECT_ID
AND cl.column_id = ic.column_id
WHERE ic.OBJECT_ID = ix.OBJECT_ID
AND ic.index_id = ix.index_id
ORDER BY ic.key_ordinal,
ic.index_column_id) k) ic
WHERE ix.index_id > 0
AND pr.partition_number = 1
AND o.OBJECT_ID = OBJECT_ID(LTRIM(RTRIM(@ObjectName))))
SELECT ObjectType,
SchemaName,
ObjectName,
IndexName,
IndexType,
ROWS,
index_id AS IndexId,
is_unique AS [Unique],
is_primary_key AS PK,
is_unique_constraint AS UnqC,
IGNORE_DUP_KEY AS IgnoreDup,
is_disabled AS [Disabled],
KeyCnt,
KeyColumns,
IncludeCnt,
IncludeColumns,
filter_definition AS Filter,
PartitionedCol,
data_compression AS Compression,
DataspaceType,
Dataspace,
fill_factor AS [FillFactor],
is_padded AS PadIndex,
ALLOW_ROW_LOCKS AS AllowRowLocks,
ALLOW_PAGE_LOCKS AS AllowPageLocks,
ObjectId
FROM IndexColumns ic
GO
EXEC sys.sp_ms_marksystemobject sp_index
GO