(8.0.22)
select table_name,partition_name,partition_description
from information_schema.partitions
where table_schema = 'test'
order by table_name,partition_name
;
(19c)
select table_name,partition_name,high_value
from user_tab_partitions
order by table_name,partition_name
;
(15)
https://stackoverflow.com/questions/56595169/how-to-retrieve-partitioning-boundaries
\d+ range
SELECT t.oid::regclass AS partition,
pg_get_expr(t.relpartbound, t.oid) AS bounds
FROM pg_inherits AS i
INNER JOIN pg_class AS t
ON t.oid = i.inhrelid
WHERE i.inhparent = 'tab1'::regclass
order by t.oid;
(2019)
-- パーティション関数の境界値確認
select * from sys.partition_range_values;
-- パーティション関数確認
select * from sys.partition_functions;
-- パーティションスキーマ確認
select * from sys.partition_schemes;
-- パーティションスキーマのデータスペースIDから対象テーブルを確認
select object_name(object_id),data_space_id,*
from sys.indexes
where data_space_id = 65601
;
※SSMSからテーブルのDDLを取得した場合もパーティション定義は含まれない
select distinct object_name(i.object_id) table_name ,
pf.name partitionfunction,
ps.name partitionscheme,
rv.value range_value
from sys.indexes i
inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
inner join sys.partition_functions pf on pf.function_id = ps.function_id
inner join sys.partition_range_values rv on pf.function_id = rv.function_id
;
select object_name(object_id) table_name,* from sys.indexes where object_name(object_id) = 'tab1';
select * from sys.partition_schemes where data_space_id = 65604;
select * from sys.partition_functions where function_id = 65539;
select * from sys.partition_range_values where function_id = 65539;
