Список секционированных объектов SQL Server

добавлено: 14 дек 12
понравилось:0
просмотров: 1696
комментов: 0

теги:

Автор: Andraptor

Сценарий помогает найти в текущей базе данных секционированные объекты, а так же схемы и функции секционирования, использованные при их создании.

--Список секционированных объектов
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;

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии