MySQL and Postgres Assessment Scripts

mysql.sql

SELECT t.table_catalog, t.table_schema, t.table_name,
	CASE WHEN primary_key_columns IS NOT NULL THEN 'true' ELSE 'false' END AS has_primary_key, primary_key_columns,
	CASE WHEN unique_key_columns IS NOT NULL THEN 'true' ELSE 'false' END AS has_unique_key, unique_key_columns,
	t.TABLE_ROWS as estimate_rows,
	ROUND(((data_length + index_length) / 1024 / 1024), 2) as table_size_mb
FROM information_schema.TABLES t
LEFT JOIN (
	SELECT TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(COLUMN_NAME SEPARATOR ',') AS primary_key_columns
	FROM information_schema.`COLUMNS` c WHERE COLUMN_KEY IN ('PRI')
	GROUP BY TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, COLUMN_KEY
) pk ON t.TABLE_CATALOG = pk.TABLE_CATALOG AND t.TABLE_SCHEMA = pk.TABLE_SCHEMA AND t.TABLE_NAME = pk.TABLE_NAME
LEFT JOIN (
	SELECT TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(COLUMN_NAME SEPARATOR ',') AS unique_key_columns
	FROM information_schema.`COLUMNS` c WHERE COLUMN_KEY IN ('UNI')
	GROUP BY TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, COLUMN_KEY
) uk ON t.TABLE_CATALOG = uk.TABLE_CATALOG AND t.TABLE_SCHEMA = uk.TABLE_SCHEMA AND t.TABLE_NAME = uk.TABLE_NAME
WHERE t.TABLE_TYPE ='BASE TABLE' AND t.TABLE_SCHEMA IN ('myschema')
ORDER BY t.table_catalog, t.table_schema, t.table_name;

postgres-using-pg_constraint.sql

-- using pg_constraint
with tbl as (
	select table_catalog, table_schema, table_name
	from information_schema."tables" t
	where table_type = 'BASE TABLE'
	and table_schema in ('public')
)
,tbl_constraints as (
SELECT nsp.nspname as table_schema,
	   ix.indrelid::regclass::text as table_name,
       cl.relname as constraint_name,
       'co.conname' AS primary_key,
       case when ix.indisprimary then 'p' when ix.indisunique then 'u' end AS constraint_type,
       a.attnum as position,
       a.attname::text as key_column,
       cl.reltuples as row_estimate
from pg_index ix
join pg_class cl on ix.indrelid = cl."oid" 
JOIN pg_namespace nsp on nsp.oid = cl.relnamespace
LEFT JOIN pg_attribute a ON a.attrelid = ix.indrelid
                          AND a.attnum = ANY(ix.indkey)
                          AND a.attnum > 0
)
,primary_keys as (
	select table_schema,table_name,constraint_name, string_agg(key_column, ',' order by position) as primary_key_columns
	from tbl_constraints
	where constraint_type = 'p'
	group by table_schema,table_name,constraint_name
)
,unique_keys as (
	select table_schema,table_name,constraint_name, string_agg(key_column, ',' order by position) as unique_key_columns
	from tbl_constraints
	where constraint_type = 'u'
	group by table_schema,table_name,constraint_name
)
,tbl_size as (
  SELECT t1.schemaname as table_schema,
         t1.relname as table_name,
         t2.n_live_tup as estimate_rows,
         pg_size_pretty(pg_total_relation_size(t1.relid)) as table_size
  from pg_catalog.pg_statio_user_tables t1
  left join pg_stat_all_tables t2 on t1.schemaname=t2.schemaname and t1.relname = t2.relname
)
select 	tbl.*,
		case when pk.primary_key_columns is null then false else true end as has_primary_key,
		pk.primary_key_columns,
		case when uk.unique_key_columns is null then false else true end as has_unique_key,
		uk.unique_key_columns,
		ts.estimate_rows,
		ts.table_size
from tbl
left join primary_keys pk on tbl.table_schema = pk.table_schema and tbl.table_name = pk.table_name
left join unique_keys uk on tbl.table_schema = uk.table_schema and tbl.table_name = uk.table_name
left join tbl_size ts on tbl.table_schema = ts.table_schema and tbl.table_name = ts.table_name
order by 1,2,3;