Postgresql 에서 자주 사용하는데 없어서 만들어 놓은 매크로..
빈 스페이스로 조회 할 경우 전체 목록을 보여주고 특정 범위로 선택해서 조회 가능 함..
SQL server management studio (SSMS) 에서 사용하는 단축키로 등록 해서 사용함
alt + f1 sp_help
| SELECT Quote_ident(nspname) || '.' || Quote_ident(relname) AS table_name,
Quote_ident(attname) AS field_name,
Format_type(atttypid, atttypmod) AS field_type,
CASE WHEN attnotnull THEN ' NOT NULL' ELSE '' END AS null_constraint,
CASE WHEN atthasdef THEN 'DEFAULT ' || (
SELECT Pg_get_expr(adbin, attrelid)
FROM pg_attrdef
WHERE adrelid=attrelid
AND adnum = attnum )::text
ELSE '' END AS dafault_value,
CASE
WHEN NULLIF(confrelid, 0) IS NOT NULL THEN confrelid::regclass::text || '( ' || array_to_string( array
(
select quote_ident( fa.attname )
FROM pg_attribute AS fa
WHERE fa.attnum = ANY ( confkey )
AND fa.attrelid = confrelid
ORDER BY fa.attnum ), ',' )
|| ' )'
ELSE '' END AS references_to
FROM pg_attribute
LEFT OUTER JOIN pg_constraint
ON conrelid = attrelid
AND attnum = conkey[1]
AND array_upper( conkey,1) = 1
INNER JOIN pg_class
ON pg_class.oid = pg_attribute.attrelid
INNER JOIN pg_namespace
ON pg_namespace.oid = pg_class.relnamespace
WHERE (
1 = CASE WHEN btrim( '$SELECTION$' ) = '' THEN 1 ELSE 0 END
OR pg_class.oid = btrim( '$SELECTION$' )::regclass::oid
) AND (
1 = CASE WHEN btrim( '$SELECTION$' ) = '' THEN 0 ELSE 1 END
OR attrelid > 1262
) AND attnum > 0
AND NOT attisdropped
AND Quote_ident(nspname) NOT IN ('pg_catalog', 'pg_toast')
ORDER BY attrelid, attnum; |
alt + f2 sp_helpindex
| SELECT
n.nspname as "schema",
t.relname as "table",
c.relname as "index",
pg_get_indexdef(indexrelid) as "def"
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
INNER JOIN pg_catalog.pg_index i
ON i.indexrelid = c.oid
INNER JOIN pg_catalog.pg_class t
ON i.indrelid = t.oid
WHERE c.relkind = 'i'
and n.nspname not in ('pg_catalog', 'pg_toast')
and pg_catalog.pg_table_is_visible(c.oid)
and (1 = CASE WHEN btrim( '$SELECTION$' ) = '' THEN 1 ELSE 0 END OR t.relname = btrim( '$SELECTION$' ))
ORDER BY
n.nspname,
t.relname,
c.relname; |
