(5.6)
drop table tab1;
create table tab1
( col1 int
, col2 int
, col3 int
);
create index ind2 on tab1(col2,col3);
alter table tab1 add constraint tab1pk primary key (col1,col2);
select index_name,seq_in_index,column_name
from information_schema.statistics
where table_schema = 'test'
and table_name = 'tab1'
order by index_name,column_name
;
--全インデックス
select index_name,group_concat(column_name order by seq_in_index separator ',') column_name
from information_schema.statistics
where table_schema = 'test'
and table_name = 'tab1'
group by index_name
order by index_name
;
--主キーのみ
select index_name,group_concat(column_name order by seq_in_index separator ',') column_name
from information_schema.statistics
where table_schema = 'test'
and table_name = 'tab1'
and index_name = 'PRIMARY'
group by index_name
order by index_name
;
(12cR1)
drop table tab1 purge;
create table tab1
( col1 int
, col2 int
, col3 int
);
create unique index ind1 on tab1(col1,col2);
create index ind2 on tab1(col2,col3);
alter table tab1 add constraint tab1pk primary key (col1,col2) using index ind1;
select A.index_name,A.column_position,A.column_name
from dba_ind_columns A
where A.table_owner = 'TEST'
and A.table_name = 'TAB1'
order by A.index_name,A.column_position
;
--全インデックス
select A.index_name,listagg(A.column_name,',') within group ( order by A.column_position) column_name
from dba_ind_columns A
where A.table_owner = 'TEST'
and A.table_name = 'TAB1'
group by A.index_name
order by A.index_name
;
--主キーのみ
select A.index_name,listagg(A.column_name,',') within group ( order by A.column_position) column_name
from dba_ind_columns A
inner join dba_constraints B
on A.table_owner = B.owner
and A.table_name = B.table_name
and B.constraint_type = 'P'
and A.index_name = B.index_name
where A.table_owner = 'TEST'
and A.table_name = 'TAB1'
group by A.index_name
order by A.index_name
;
(9.4)
drop table tab1 cascade;
create table tab1
( col1 int
, col2 int
, col3 int
);
create unique index ind1 on tab1(col1,col2);
create index ind2 on tab1(col2,col3);
alter table tab1 add constraint tab1pk primary key using index ind1;
select indexname,indexdef,position('(' in indexdef),position(')' in indexdef)
,replace(substring(indexdef from (position('(' in indexdef))+1 for (position(')' in indexdef) - position('(' in indexdef)-1)),' ','')
from pg_indexes
where schemaname = 'public'
and tablename ='tab1'
order by indexname
;
--全インデックス
select A.indexname
,replace(substring(A.indexdef from (position('(' in A.indexdef))+1 for (position(')' in A.indexdef) - position('(' in A.indexdef)-1)),' ','')
from pg_indexes A
where A.schemaname = 'public'
and A.tablename ='tab1'
order by A.indexname
;
--主キーのみ
select A.indexname
,replace(substring(A.indexdef from (position('(' in A.indexdef))+1 for (position(')' in A.indexdef) - position('(' in A.indexdef)-1)),' ','')
from pg_indexes A
inner join information_schema.table_constraints B
on A.schemaname = B.table_schema
and A.tablename = B.table_name
and B.table_catalog = 'test'
and B.constraint_type = 'PRIMARY KEY'
and A.indexname = B.constraint_name
where A.schemaname = 'public'
and A.tablename ='tab1'
order by A.indexname
;
(2017)
drop table tab1;
create table tab1
( col1 int not null
, col2 int not null
, col3 int
);
create index ind2 on tab1(col2,col3);
alter table tab1 add constraint tab1pk primary key(col1,col2);
select
A.name index_name
,col_name(B.object_id,B.column_id) column_name
,B.key_ordinal
,A.is_primary_key
from sys.indexes A
inner join sys.index_columns B
on A.object_id = B.object_id
and A.index_id = B.index_id
where object_name(A.object_id) = 'tab1'
order by A.name,B.key_ordinal
;
--全インデックス
select
A.name index_name
,string_agg( col_name(B.object_id,B.column_id) ,',') within group ( order by B.key_ordinal) index_column
from sys.indexes A
inner join sys.index_columns B
on A.object_id = B.object_id
and A.index_id = B.index_id
where object_name(A.object_id) = 'tab1'
group by A.name
order by A.name
;
--主キーのみ
select
A.name index_name
,string_agg( col_name(B.object_id,B.column_id) ,',') within group ( order by B.key_ordinal) index_column
from sys.indexes A
inner join sys.index_columns B
on A.object_id = B.object_id
and A.index_id = B.index_id
where object_name(A.object_id) = 'tab1'
and A.is_primary_key = 1
group by A.name
order by A.name
;
