Monthly Archives: September 2010

To Find the list of Foreign key dependencies on a table

Query: SELECT FK.TABLE_NAME, CU.COLUMN_NAME, PK.TABLE_NAME, PT.COLUMN_NAME, C.CONSTRAINT_NAME FROM       INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN      INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN  ( SELECT      i1.TABLE_NAME, i2.COLUMN_NAME … Continue reading

Posted in Uncategorized | Leave a comment

Indexes

1. to find true duplicate indexes, Ie. same table, same columns, same column order. CREATE AGGREGATE array_accum (anyelement) ( sfunc = array_append, stype = anyarray, initcond = ‘{}’ ); select indrelid::regclass, array_accum(indexrelid::regclass) from pg_index group by indrelid, indkey having count(*) … Continue reading

Posted in Uncategorized | Leave a comment