설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 94건, 최근 0 건
   

인덱스가 하나도 없거나 PRIMARY KEY 가 없는 테이블 찾기

글쓴이 : PostgresDBA 날짜 : 2012-12-21 (금) 23:08 조회 : 8046
postgres@[local]:5432:postgres] 
SQL> create table x(x int);
CREATE TABLE
postgres@[local]:5432:postgres] 
SQL> SELECT c.table_schema, c.table_name, c.table_type
FROM information_schema.tables c
WHERE  c.table_schema NOT IN('information_schema', 'pg_catalog') AND c.table_type = 'BASE TABLE'
AND NOT EXISTS(SELECT i.tablename
    FROM pg_catalog.pg_indexes i
    WHERE i.schemaname = c.table_schema
    AND i.tablename = c.table_name AND indexdef LIKE '%UNIQUE%')
AND
NOT EXISTS (SELECT cu.table_name
    FROM information_schema.key_column_usage cu
    WHERE cu.table_schema = c.table_schema AND
       cu.table_name = c.table_name)
ORDER BY c.table_schema, c.table_name;
 table_schema | table_name | table_type 
--------------+------------+------------
 public       | x          | BASE TABLE
(1 row)

postgres@[local]:5432:postgres] 
SQL> 


   

postgresdba.com