CODEWITHSUNDEEP
mysqlsqlpostgresqlrelational-databaseintrospection
JWL
JWL

Reputation: 14201

Find a table with a column name matching a given pattern

This might sound a bit un-realistic, but I've recently had to study the database structure of a system am maintaining remotely, and it hit me that there were some occassions when I wanted to find all columns that were linked to a given table (see, the original devs didn't create explicit relations, but instead have relations encoded in the column names!).

For example, assuming all columns referring to a column parent.parent have the form table.somefield_parent as the means of documenting a relationship between the two fields, how do I find all such relationships (and the tables involved) using say the standard SQL commands / in-built functions?

Any solutions for MySQL or PostgreSQL are welcome.

NOTE :

Upvotes: 3

Views: 4234

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656706

In PostgreSQL you can query the system catalog tables.

You can also query the information_schema, which consists of views providing the standard SQL way of displaying the information. Querying the system catalogs is usually much faster, though.

To find the children of one particular parent column:

SELECT n.nspname AS schema_name
     , c.relname AS table_name
     , a.attname AS column_name
FROM   pg_catalog.pg_attribute a
JOIN   pg_catalog.pg_class c ON c.oid = a.attrelid
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  a.attname ~~ E'%\\_parent'
AND    NOT a.attisdropped
AND    c.relkind = 'r'
AND    nspname !~~ E'pg\\_%';

To find all related column according to the description:

SELECT n.nspname AS parrent_schema
     , c.relname AS parrent_table
     , a.attname AS parrent_column
     , n1.nspname AS child_schema
     , c1.relname AS child_table
     , a1.attname AS child_column
FROM   pg_catalog.pg_attribute a
JOIN   pg_catalog.pg_class c ON c.oid = a.attrelid
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
JOIN   pg_catalog.pg_attribute a1 ON a.attname = substring(a1.attname, '_(.*?)$')
JOIN   pg_catalog.pg_class c1 ON c1.oid = a1.attrelid
JOIN   pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace
WHERE  c.relkind = 'r'
AND    c1.relkind = 'r'
AND    n.nspname !~~ E'pg\\_%'
AND    n1.nspname !~~ E'pg\\_%'
AND    NOT a.attisdropped
AND    NOT a1.attisdropped
-- AND    n.nspname = 'public'   -- to narrow it down to a specific schema
-- AND    n1.nspname = 'public'   -- to narrow it down to a specific schema
ORDER  BY 1,2,3,4,5,6

This assumes that the parrent columns don't have _ in the name.
The key element is the join condition:

a.attname = substring(a1.attname, '_(.*?)$')

Upvotes: 1

John Jeffery
John Jeffery

Reputation: 1020

Try querying the information_schema.columns view. It works in MySQL, but I have not tried it in Postgres. The information_schema schema is part of the ANSI SQL standard, so Postgres will quite probably have it as well.

Other views in information_schema include information_schema.tables, which is useful as well

Upvotes: 7

user254875486
user254875486

Reputation: 11240

If you use MySQL you can use the database information_schema for that. This database contains all the meta-data for the databases on that server. As far as I know every database user has to have access to that database.

Upvotes: 0

Related Questions