📋 Database Schema Extraction Guide

-- Get all table names first
SHOW TABLES;

-- Then for each table, run:
SHOW CREATE TABLE your_table_name;

SELECT 
    CONCAT('CREATE TABLE ', TABLE_NAME, ' (\n',
           GROUP_CONCAT(
               CONCAT('    ', COLUMN_NAME, ' ', COLUMN_TYPE,
                      IF(IS_NULLABLE = 'NO', ' NOT NULL', ''),
                      IF(COLUMN_KEY = 'PRI', ' PRIMARY KEY', ''),
                      IF(EXTRA != '', CONCAT(' ', EXTRA), ''))
               SEPARATOR ',\n'
           ),
           '\n);') AS create_statement
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
GROUP BY TABLE_NAME;

pg_dump -U username -d database_name --schema-only > schema.sql

SELECT 
    'CREATE TABLE ' || table_name || ' (' || 
    string_agg(
        column_name || ' ' || data_type || 
        CASE WHEN character_maximum_length IS NOT NULL 
             THEN '(' || character_maximum_length || ')' 
             ELSE '' END ||
        CASE WHEN is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END,
        ', '
    ) || ');' AS create_statement
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name;

SELECT 
    'CREATE TABLE ' + TABLE_NAME + ' (' + CHAR(13) +
    STUFF((
        SELECT ',' + CHAR(13) + '    ' + 
               COLUMN_NAME + ' ' + 
               DATA_TYPE + 
               CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL 
                    THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' 
                    ELSE '' END +
               CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END
        FROM INFORMATION_SCHEMA.COLUMNS c2
        WHERE c2.TABLE_NAME = c1.TABLE_NAME
        FOR XML PATH('')
    ), 1, 1, '') + CHAR(13) + ');' AS CreateStatement
FROM INFORMATION_SCHEMA.COLUMNS c1
GROUP BY TABLE_NAME;

-- For each table:
sp_help 'your_table_name';

-- In SQLite command line:
.schema

-- Or for a specific table:
.schema your_table_name

SELECT sql FROM sqlite_master 
WHERE type='table' 
AND name NOT LIKE 'sqlite_%';

-- For a specific table:
SELECT DBMS_METADATA.GET_DDL('TABLE', 'YOUR_TABLE_NAME') 
FROM DUAL;

-- For all tables in your schema:
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name) 
FROM user_tables;

SELECT 
    'CREATE TABLE ' || table_name || ' (' ||
    LISTAGG(
        column_name || ' ' || data_type ||
        CASE WHEN data_length IS NOT NULL 
             THEN '(' || data_length || ')' 
             ELSE '' END ||
        CASE WHEN nullable = 'N' THEN ' NOT NULL' ELSE '' END,
        ', '
    ) WITHIN GROUP (ORDER BY column_id) || ');' AS create_statement
FROM user_tab_columns
GROUP BY table_name;

-- For a specific table:
SELECT GET_DDL('TABLE', 'DATABASE_NAME.SCHEMA_NAME.TABLE_NAME');

-- Get all tables in current database and schema
SELECT 
    'CREATE TABLE ' || TABLE_NAME || ' (' ||
    LISTAGG(
        COLUMN_NAME || ' ' || DATA_TYPE ||
        CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL 
             THEN '(' || CHARACTER_MAXIMUM_LENGTH || ')' 
             ELSE '' END ||
        CASE WHEN NUMERIC_PRECISION IS NOT NULL 
             THEN '(' || NUMERIC_PRECISION || 
                  CASE WHEN NUMERIC_SCALE IS NOT NULL 
                       THEN ',' || NUMERIC_SCALE 
                       ELSE '' END || ')' 
             ELSE '' END ||
        CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END,
        ', '
    ) WITHIN GROUP (ORDER BY ORDINAL_POSITION) || ');' AS create_statement
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_SCHEMA_NAME'
GROUP BY TABLE_NAME;

-- First, get list of all tables
SHOW TABLES IN SCHEMA YOUR_SCHEMA_NAME;

-- Then for each table, run:
SELECT GET_DDL('TABLE', 'YOUR_DATABASE.YOUR_SCHEMA.TABLE_NAME');

-- Get DDL for all tables in a schema
SELECT GET_DDL('SCHEMA', 'YOUR_DATABASE.YOUR_SCHEMA');