Here are some notes I’ve been making from the book
SQL Cookbook Query Solutions and Techniques for All SQL Users(Anthony Molinaro, Robert de Graaf), specifically from chapter 5.
Go Back to Title Page
Chapter 5
SELECT table_name FROM information_schema.tables WHERE table_schema = ‘SMEAGOL’
This is a means of getting the names of tables where the table_schema is SMEAGOL. This works for both mysql and postgres.
Suppose we want to find columns, their data types and their numeric position in the table employee: SELECT column_name, data_type, ordinal_position FROM information_schema.columns WHERE table_schema = ‘SMEAGOL’ AND table_name = ‘employee’
Suppose you want to list indexes, columns and column positions (if available) in the index for a given table:
postgres: SELECT a.tablename, a.indexname, b.column_name FROM pg_catalog.pg_indexes a, information_schema.columns b WHERE a.schemaname = ‘SMEAGOL’ AND a.tablename = b.table_name
mysql: SHOW index FROM emp
Suppose you want to list the constraints defined for a table in some schema and the columns they are defined on:
SELECT a.table_name, a.constraint_name, b.column_name, a.constraint_type FROM information_schema.table_constraints a, information_schema.key_column_usage b WHERE a.table_name = ‘insert_table_name_here’ AND a.table_schema = ‘SMEAGOL’ AND a.table_name = b.table_name AND a.table_schema = b.table_schema AND a.constraint_name = b.constraint_name
Suppose you want ot list tables that have foreign key columns.
postgres: SELECT fkeys.table_name, fkeys.constraint_name, fkeys.column_name, ind_cols.indexname FROM ( SELECT a.constraint_schema, a.table_name, a.column_name FROM information_schema.key_column_usage a, information_schema.referential_constraints b WHERE a.constraint_name = b.constraint_name AND a.constraint_schema = b.constraint_schema AND a.constraint_schema = “SMEAGOL” AND a.table_name = “insert_table_name_here” ) AS fkeys left join (SELECT a.schemaname, a.tablename, a.indexname, b.column_name FROM pg_catalog.pg_indexes a, information_schema.columns b WHERE a.tablename = b.table_name AND a.schemaname = b.table_schema ) AS ind_cols ON ( fkeys.constraint_schema = ind_cols.schemaname AND fkeys.table_name = ind_cols.tablename AND fkeys.column_name = ind_cols.column_name ) WHERE ind_cols.indexname IS NULL
mysql: the SHOW INDEX command can be used to retrieve index information.
A database engine (or storage engine) is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database.