An enhance script to check partition tables under all schemas

An enhance script to check partition tables under all schemas

DROP VIEW [partition_show]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE view [partition_show]

as

SELECT distinct OBJECT_NAME(T2.object_id) TABLE_NAME

,T1.partition_number

,T4.name as “function_name”

,T3.name as “schema_name”

,T7.name File_group_name

,T1.rows

,CASE boundary_value_on_right

WHEN 1 THEN ‘less than’

ELSE ‘less than or equal to ‘ END as ‘comparision’

–,CONVERT(varchar(100), T5.value, 112) value

,T5.value

FROM sys.partitions T1

INNER JOIN sys.indexes T2

ON T1.object_id = T2.object_id

INNER JOIN sys.partition_schemes T3

ON T2.data_space_id = T3.data_space_id

INNER JOIN sys.partition_functions T4

ON T3.function_id = T4.function_id

LEFT JOIN sys.partition_range_values T5

ON T4.function_id = T5.function_id

AND T1.partition_number = T5.boundary_id

INNER JOIN sys.destination_data_spaces T6

ON T6.partition_scheme_id = T3.data_space_id

AND T6.destination_id = T1.partition_number

INNER JOIN sys.filegroups T7

ON T6.data_space_id = T7.data_space_id

where T2.object_id in (select t.object_id from sys.tables as t inner join sys.indexes as i on t.object_id=i.object_id and i.type in (0,1) inner join sys.partition_schemes ps on i.data_space_id=ps.data_space_id)

AND T1.index_id<=1

—————————————————————————————————————————–

Difference

——————————————————————————————————————————

original part:

select OBJECT_ID(t.name) from sys.tables as t inner join sys.indexes as i on t.object_id=i.object_id and i.type in (0,1) inner join sys.partition_schemes ps on i.data_space_id=ps.data_space_id

new part

selectt.object_id from sys.tables as t inner join sys.indexes as i on t.object_id=i.object_id and i.type in (0,1) inner join sys.partition_schemes ps on i.data_space_id=ps.data_space_id

Related:

Leave a Reply