Query to check tables list with Primary Key

 

/* Query to check tables list with Primary Key*/

———————————————————————————————————–

select kcu.table_schema,

kcu.table_name,

tco.constraint_name,

kcu.ordinal_position as position,

kcu.column_name as key_column

from information_schema.table_constraints tco

join information_schema.key_column_usage kcu

on kcu.constraint_name = tco.constraint_name

and kcu.constraint_schema = tco.constraint_schema

and kcu.constraint_name = tco.constraint_name

where tco.constraint_type <> ‘PRIMARY KEY’ and kcu.table_schema<>’sys’

order by kcu.table_schema,

kcu.table_name,

position;

/* Query to check tables list without Primary Key*/

———————————————————————————————————–

select tab.table_schema,

tab.table_name

from information_schema.tables tab

left join information_schema.table_constraints tco

on tab.table_schema = tco.table_schema

and tab.table_name = tco.table_name

and tco.constraint_type = ‘PRIMARY KEY’

where tab.table_type = ‘BASE TABLE’

and tab.table_schema not in (‘pg_catalog’,’sys’,’information_schema’)

and tco.constraint_name is null

order by table_schema,

table_name;

SELECT t.table_schema || ‘.’ || t.table_name SchemaName_TableName

FROM information_schema.tables t

WHERE

(table_catalog, table_schema, table_name) NOT IN (

SELECT tc.table_catalog, tc.table_schema, tc.table_name

FROM information_schema.table_constraints tc

WHERE constraint_type = ‘PRIMARY KEY’) AND t.table_type = ‘BASE TABLE’ AND

t.table_schema NOT IN (‘information_schema’, ‘pg_catalog’, ‘pgq’, ‘londiste’,’sys’);