Сценарий помогает найти в текущей базе данных секционированные объекты, а так же схемы и функции секционирования, использованные при их создании.
--Список секционированных объектов
SELECT OBJECT_SCHEMA_NAME(i.object_id)+'.'+OBJECT_NAME(i.object_id) as ObjectName
, i.name as IndexNname
, i.type_desc as IndexType
, pf.fanout as PartitionsCount
, STUFF((SELECT CAST((SELECT (SELECT ','+c.name
from sys.index_columns as ic
INNER JOIN sys.columns as c on c.object_id = ic.object_id and c.column_id = ic.column_id
WHERE ic.object_id = i.object_id and ic.index_id = i.index_id
AND ic.partition_ordinal > 0
order by ic.partition_ordinal
FOR XML PATH (''), Type
) AS SchemeCol
) AS nvarchar(MAX)
)),1,1,'') as PartitionColumns
,'CREATE PARTITION FUNCTION ['+pf.name+'] ('+t.name + CASE WHEN pp.system_type_id in (167,175,231,239,256) THEN '('+CONVERT(nvarchar(10),pp.max_length)+')) ' ELSE +') ' END
+ 'AS '+pf.type_desc COLLATE Cyrillic_General_CI_AS + CASE pf.boundary_value_on_right WHEN 1 THEN ' RIGHT ' ELSE ' LEFT ' END + 'FOR VALUES ('
+ STUFF((SELECT CAST((SELECT (SELECT CASE WHEN pp.system_type_id in (40,41,42,43,58,61,167,175,231,239,256) THEN ',N'''+CONVERT(nvarchar(100),prv.value,120) + ''''
ELSE ','+CONVERT(nvarchar(100),prv.value)
END
FROM sys.partition_range_values AS prv
INNER JOIN sys.partition_parameters as pp on prv.function_id = pp.function_id
WHERE pf.function_id = prv.function_id
FOR XML PATH (''), Type
) AS FunCol
) AS nvarchar(MAX)
)),1,1,'') + ')' as PartitionFunction
,'CREATE PARTITION SCHEME ['+ps.name+'] AS PARTITION ['+pf.name+'] TO ('
+ STUFF((SELECT CAST((SELECT (SELECT ','+ds.name
from sys.destination_data_spaces as dds
INNER JOIN sys.data_spaces as ds on dds.data_space_id = ds.data_space_id
WHERE ps.data_space_id = dds.partition_scheme_id
order by ps.data_space_id, dds.destination_id
FOR XML PATH (''), Type
) AS SchemeCol
) AS nvarchar(MAX)
)),1,1,'') + ')' as PartitionScheme
FROM sys.partition_functions AS pf
INNER JOIN sys.partition_parameters as pp ON pf.function_id = pp.function_id
INNER JOIN sys.types as t ON pp.system_type_id = t.system_type_id
INNER JOIN sys.partition_schemes as ps ON pf.function_id = ps.function_id
INNER JOIN sys.indexes as i ON ps.data_space_id = i.data_space_id
ORDER BY ObjectName;