Notes

-- Parámetros
DECLARE @TableName NVARCHAR(128) = 'NombreDeTuTabla';

-- Consulta para obtener información de índices y columnas
SELECT
tbl.name AS TableName,
col.name AS ColumnName,
ind.name AS IndexName,
ind.type_desc AS IndexType,
ind.is_unique AS IsUnique,
ind.is_primary_key AS IsPrimaryKey,
ind.is_unique_constraint AS IsUniqueConstraint,
CASE
WHEN pns.name IS NOT NULL THEN pns.name
ELSE 'Not Partitioned'
END AS PartitionSchemeName,
CASE
WHEN pns.name IS NOT NULL THEN 'Yes'
ELSE 'No'
END AS IsPartitioned
FROM
sys.tables AS tbl
INNER JOIN
sys.columns AS col ON tbl.object_id = col.object_id
LEFT JOIN
sys.index_columns AS ic ON col.object_id = ic.object_id AND col.column_id = ic.column_id
LEFT JOIN
sys.indexes AS ind ON ic.object_id = ind.object_id AND ic.index_id = ind.index_id
LEFT JOIN
sys.partitions AS pt ON pt.object_id = tbl.object_id
LEFT JOIN
sys.partition_schemes AS pns ON pns.data_space_id = ind.data_space_id
WHERE
tbl.name = @TableName
ORDER BY
col.name, ind.name;