Created
June 12, 2015 03:32
-
-
Save xivSolutions/1a2ebfa084f7911b06b9 to your computer and use it in GitHub Desktop.
MassiveJS Get Tables with Key Info v1
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
deallocate tables; | |
prepare tables(text, text, text) as | |
SELECT c.TABLE_NAME, c.COLUMN_NAME, kcu.CONSTRAINT_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, tc.CONSTRAINT_TYPE, | |
CASE tc.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN CAST(1 AS BIt) ELSE CAST(0 AS Bit) END AS IsPrimaryKey, | |
(CASE ( | |
(SELECT CASE (LENGTH(pg_get_serial_sequence(c.TABLE_NAME, c.COLUMN_NAME)) > 0) WHEN true THEN 1 ELSE 0 END) + | |
(SELECT CASE (SELECT pgc.relkind FROM pg_class pgc WHERE pgc.relname = c.TABLE_NAME || '_' || c.COLUMN_NAME || '_' || 'seq') WHEN 'S"' THEN 1 ELSE 0 END)) | |
WHEN 0 THEN false ELSE true END) AS IsAuto, c.column_default | |
FROM information_schema.columns c | |
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu | |
ON c.TABLE_SCHEMA = kcu.CONSTRAINT_SCHEMA AND c.TABLE_NAME = kcu.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME | |
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc | |
ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME | |
where | |
tc.constraint_type = 'PRIMARY KEY' | |
and | |
((case -- allow specific schemas (none or '' assumes all): | |
when $1 ='' then 1=1 | |
else tc.table_schema = any(string_to_array(replace($1, ' ', ''), ',')) end) | |
and | |
(case -- blacklist tables using LIKE by fully-qualified name (no schema assumes public): | |
when $2 = '' then 1=1 | |
else replace((tc.table_schema || '.'|| tc.table_name), 'public.', '') not like all(string_to_array(replace($2, ' ', ''), ',')) end)) | |
or | |
(case -- make exceptions for specific tables, with fully-qualified name or wildcard pattern (no schema assumes public). | |
when $3 = '' then 1=0 | |
-- Below can use '%' as wildcard. Change 'like' to '=' to require exact names: | |
else replace((tc.table_schema || '.'|| tc.table_name), 'public.', '') like any(string_to_array(replace($3, ' ', ''), ',')) end) | |
order by tc.table_schema, | |
tc.table_name, | |
kcu.position_in_unique_constraint; | |
execute tables('', '', ''); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment