Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/* 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment