Truncate Table With Partitions - aligned/ non-aligned indexes

Question

SQL Server 2016 introduced a modification to the TRUNCATE TABLE statement which allows you to truncate a specific partition or set of partitions from a table:

TRUNCATE TABLE < tablename > WITH (PARTITIONS (< partition_id >));

An article about the TRUNCATE TABLE WITH PARTITIONS contains the next note:

To start using the new TRUNCATE TABLE statement option, make sure that all your table indexes are aligned with the source table. If there are non-aligned indexes in your table, drop or disable these indexes, truncate the partition(s) you want then rebuild the indexes again.

Does SQL Server 2016 provide an easy way (e.g. a query) to determine if the indexes of the partitioned tables are all aligned? (I want to avoid having to drop or disable indexes.)

---

Update 1

Example output of link:

*One index consists of 2 columns, but only one column is used in the partition function.

Is the index PK_TEST1 aligned or non-aligned?*

• index_name: PK_TEST1
• partition_function: psTEST1
• partition_ordinal: 1
• key_ordinal: 1
• is_included_column: 0
• columnName: COLUMN1

#

• index_name: PK_TEST1
• partition_function: psTEST1
• partition_ordinal: 0
• key_ordinal: 2
• is_included_column: 0
• columnName: COLUMN2

Answer 1

I put this query together for you that should show you what you want. You can add additional where clauses to trim it further if you need to.

;WITH CTE_PartCount AS
    (
    SELECT P.object_id
        , P.index_id
        , COUNT(P.partition_number) AS PartitionCount
    FROM sys.partitions AS P
    GROUP BY P.object_id
        , P.index_id
    )
, CTE_Objects AS
    (
    SELECT O.object_id
        , O.name AS ObjectName
        , S.name AS SchemaName
        , I.index_id
        , I.name AS IndexName
        , I.type_desc AS IndexType
    FROM sys.objects AS O
        INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id
        INNER JOIN sys.indexes AS I ON I.object_id = O.object_id
    WHERE O.is_ms_shipped = 0
        AND O.type_desc = 'USER_TABLE'
    )
, CTE_Summary AS
    (
    SELECT O.object_id
        , O.ObjectName
        , O.SchemaName
        , O.IndexName
        , O.IndexType
        , PC.PartitionCount
        , TablePartCount = FIRST_VALUE(PC.PartitionCount) OVER (PARTITION BY O.object_id ORDER BY O.object_id, O.index_id)
    FROM CTE_Objects AS O
        LEFT OUTER JOIN CTE_PartCount AS PC ON PC.object_id = O.object_id AND PC.index_id = O.index_id
    )
SELECT S.object_id
    , S.SchemaName
    , S.ObjectName
    , S.IndexName
    , S.IndexType
    , S.PartitionCount
    , IsPartitioned = CASE WHEN S.TablePartCount <> 1 THEN 'YES' ELSE 'NO' END
    , IsAligned = CASE WHEN S.TablePartCount = S.PartitionCount THEN 'ALIGNED' ELSE '' END
FROM CTE_Summary AS S