如何在SQL中获取指定schema下所有表及对应列名？

Absolutely! You don’t have to waste hours clicking through a GUI to get this info—SQL has you covered. The exact query varies a bit depending on your database system, so I’ll break down the most common ones for you:

PostgreSQL #

Use the standard information_schema views, which are built specifically for metadata queries like this:

SELECT 
    table_name, 
    column_name
FROM 
    information_schema.columns
WHERE 
    table_schema = 'your_target_schema' -- Replace with your actual schema name
ORDER BY 
    table_name, 
    ordinal_position; -- Ensures columns follow the same order as they appear in the table

If you’d prefer a condensed view where all columns for a table are listed in a single comma-separated string, use STRING_AGG:

SELECT 
    table_name, 
    STRING_AGG(column_name, ', ') AS column_list
FROM 
    information_schema.columns
WHERE 
    table_schema = 'your_target_schema'
GROUP BY 
    table_name
ORDER BY 
    table_name;

MySQL/MariaDB #

MySQL uses the same ANSI-standard information_schema structure as PostgreSQL. Note that in MySQL, "schema" is interchangeable with "database":

SELECT 
    table_name, 
    column_name
FROM 
    information_schema.columns
WHERE 
    table_schema = 'your_target_schema' -- Replace with your database/schema name
ORDER BY 
    table_name, 
    ordinal_position;

For comma-separated columns per table, use GROUP_CONCAT:

SELECT 
    table_name, 
    GROUP_CONCAT(column_name ORDER BY ordinal_position SEPARATOR ', ') AS column_list
FROM 
    information_schema.columns
WHERE 
    table_schema = 'your_target_schema'
GROUP BY 
    table_name;

SQL Server #

SQL Server uses its own system catalog views (though information_schema still works, system views offer more flexibility):

SELECT 
    t.name AS table_name, 
    c.name AS column_name
FROM 
    sys.tables t
JOIN 
    sys.columns c ON t.object_id = c.object_id
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    s.name = 'your_target_schema' -- Replace with your schema name
ORDER BY 
    t.name, 
    c.column_id; -- Matches the column order defined in the table

For comma-separated columns (available in SQL Server 2017 and later), use STRING_AGG:

SELECT 
    t.name AS table_name, 
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY c.column_id) AS column_list
FROM 
    sys.tables t
JOIN 
    sys.columns c ON t.object_id = c.object_id
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    s.name = 'your_target_schema'
GROUP BY 
    t.name
ORDER BY 
    t.name;

Oracle #

In Oracle, schemas are tied to user accounts. Use all_tab_columns to access tables you have permissions to view, or user_tab_columns if querying your own schema:

SELECT 
    table_name, 
    column_name
FROM 
    all_tab_columns
WHERE 
    owner = 'YOUR_TARGET_SCHEMA' -- Replace with your schema name (case-sensitive if using quoted identifiers)
ORDER BY 
    table_name, 
    column_id;

To get comma-separated columns, use LISTAGG:

SELECT 
    table_name, 
    LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id) AS column_list
FROM 
    all_tab_columns
WHERE 
    owner = 'YOUR_TARGET_SCHEMA'
GROUP BY 
    table_name
ORDER BY 
    table_name;

内容的提问来源于stack exchange，提问作者Jan