getPartitionedTables

/* returns all partitioned tables: */
SELECT OBJECT_NAME(p.[object_id]) AS 'TableName'
    , i.[name] AS 'IndexName'
    , i.index_id
    , p.[object_id]
    , au.data_space_id
    , p.partition_number
    , p.hobt_id
    , partitionds.name AS partition_filegroup
INTO #partDist
FROM sys.allocation_units au
    JOIN sys.partitions p ON au.container_id = p.[partition_id]
    LEFT JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
    LEFT JOIN sys.data_spaces partitionds ON dds.data_space_id = partitionds.data_space_id
    LEFT JOIN sys.indexes i ON p.[object_id] = i.[object_id]
    JOIN sys.partition_schemes ps ON dds.partition_scheme_id = ps.data_space_id
           AND i.data_space_id = dds.partition_scheme_id
    LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE i.index_id = ( SELECT TOP 1
                     i.index_id
                     FROM sys.indexes inner_i
                     WHERE inner_i.[object_id] = i.[object_id]);
                     
/* Check for records where partition numbers match but partition filegroup names do not: */
SELECT pd.TableName
    , pd.IndexName
    , au.data_space_id 'AllocDataSpace'
    , pd.data_space_id 'PartDataSpace'
    , au.partition_number 'AllocPartNum'
    , pd.partition_number 'PartNum'
    , au.partition_filegroup 'AuFilegroup'
    , pd.partition_filegroup 'PartFilegroup'
INTO #erroralert
FROM #allocUnits au
    JOIN #partDist pd ON au.object_id = pd.object_id
        AND au.partition_number = pd.partition_number
        AND pd.index_id = au.index_id
WHERE au.partition_filegroup <> pd.partition_filegroup
ORDER BY pd.TableName
    , au.index_id
    , au.partition_number;

IF EXISTS ( SELECT *
            FROM #erroralert )
BEGIN
    SELECT DISTINCT TableName
        , AllocDataSpace
        , PartDataSpace
        , AllocPartNum
        , PartNum
        , AuFilegroup
        , PartFilegroup
    FROM #erroralert;
    PRINT 'ALERT DBA Here';
END;