Database & Development

Database & Development

SQL Metadata Queries for Dynamic Schema Discovery

Problem

Applications often need to discover database schema information programmatically for dynamic form generation, data validation, or automated documentation. Hard-coding table and column names creates maintenance overhead when schema changes occur. Dynamic report builders, data import tools, and admin interfaces require runtime access to table structures.

Solution

Use Oracle data dictionary views (USER_TAB_COLUMNS, ALL_TAB_COLUMNS, USER_CONSTRAINTS, USER_INDEXES) to query schema metadata programmatically. Build reusable PL/SQL functions that return table structures, column definitions, and constraint information.

Implementation

-- Get all columns for a table
SELECT
    column_name,
    data_type,
    data_length,
    data_precision,
    data_scale,
    nullable,
    data_default
FROM user_tab_columns
WHERE table_name = 'MY_TABLE'
ORDER BY column_id;

Dynamic Column List Function:

CREATE OR REPLACE FUNCTION get_table_columns (
    p_table_name IN VARCHAR2,
    p_delimiter IN VARCHAR2 DEFAULT ','
) RETURN VARCHAR2
IS
    l_columns VARCHAR2(4000);
BEGIN
    SELECT LISTAGG(column_name, p_delimiter)
           WITHIN GROUP (ORDER BY column_id)
    INTO l_columns
    FROM user_tab_columns
    WHERE table_name = UPPER(p_table_name);

    RETURN l_columns;
END get_table_columns;

Query Foreign Key Relationships:

SELECT
    ac.constraint_name,
    ac.table_name as child_table,
    acc.column_name as child_column,
    ac_pk.table_name as parent_table,
    acc_pk.column_name as parent_column
FROM all_constraints ac
JOIN all_cons_columns acc ON ac.constraint_name = acc.constraint_name
JOIN all_constraints ac_pk ON ac.r_constraint_name = ac_pk.constraint_name
JOIN all_cons_columns acc_pk ON ac_pk.constraint_name = acc_pk.constraint_name
WHERE ac_pk.table_name = 'MAIN_RECORDS'
  AND ac.constraint_type = 'R';

Metadata Cache Table:

CREATE TABLE metadata_cache (
    table_name VARCHAR2(128),
    column_name VARCHAR2(128),
    data_type VARCHAR2(106),
    is_nullable VARCHAR2(1),
    cached_date TIMESTAMP DEFAULT SYSTIMESTAMP,
    PRIMARY KEY (table_name, column_name)
);