-- 테이블 목록 조회
scott@[local]:5432 scottdb#SQL> select table_name from information_schema.tables where
table_schema='public';
table_name
------------
bonus
dummy
salgrade
dept
emp
test
(6 rows)
Time: 55.985 ms
-- 테이블 구성 컬럼 조회
scott@[local]:5432 scottdb#SQL> select table_name, column_name, data_type, ordinal_position
from information_schema.columns where table_schema='public' order by table_name, ordinal_position;
table_name | column_name | data_type | ordinal_position
------------+-------------+-------------------+------------------
bonus | ename | character varying | 1
bonus | job | character varying | 2
bonus | sal | integer | 3
bonus | comm | integer | 4
dept | deptno | integer | 1
dept | dname | character varying | 2
dept | loc | character varying | 3
dummy | dummy | integer | 1
emp | empno | integer | 1
emp | ename | character varying | 2
emp | job | character varying | 3
emp | mgr | integer | 4
emp | hiredate | date | 5
emp | sal | integer | 6
emp | comm | integer | 7
emp | deptno | integer | 8
salgrade | grade | integer | 1
salgrade | losal | integer | 2
salgrade | hisal | integer | 3
test | x | integer | 1
test | y | integer | 2
(21 rows)
Time: 9.061 ms
scott@[local]:5432 scottdb#SQL>
-- 인덱스 목록및 구성 컬럼 조회
scott@[local]:5432 scottdb#SQL> select a.tablename,a.indexname,b.column_name
from pg_catalog.pg_indexes a, information_schema.columns b
where a.schemaname = 'public'
and a.tablename = b.table_name;
tablename | indexname | column_name
-----------+-----------+-------------
dept | dept_pkey | deptno
dept | dept_pkey | dname
dept | dept_pkey | loc
emp | emp_pkey | empno
emp | emp_pkey | ename
emp | emp_pkey | job
emp | emp_pkey | mgr
emp | emp_pkey | hiredate
emp | emp_pkey | sal
emp | emp_pkey | comm
emp | emp_pkey | deptno
(11 rows)
Time: 62.156 ms
scott@[local]:5432 scottdb#SQL>
