Generate SQL with Information Schema

Use MySQL information_schema to inspect table metadata and generate dynamic SQL safely.

SQL

Context

information_schema exposes metadata about database objects. In MySQL, that makes it useful for generating SQL dynamically—for example, when building migrations, admin tools, or scripts that need to adapt to a table’s shape instead of hard-coding column names. The key idea is simple: treat metadata as data. First read the table definition from information_schema, then render the SQL you need from that metadata.

Dynamic SQL Queries

COLUMNS gives you the column names for a target table:

SELECT column_name
FROM information_schema.COLUMNS
WHERE table_schema = '<schema>'
  AND table_name = '<table>'
ORDER BY ordinal_position;

Use table_schema explicitly. In multi-schema systems, omitting it can produce ambiguous results or accidentally target the wrong table.

ordinal_position preserves the real column order, which matters if you want generated SQL to match the table definition rather than a sorted list.

If you want a comma-separated column list, GROUP_CONCAT turns rows into a single string:

SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position SEPARATOR ', ')
FROM information_schema.COLUMNS
WHERE table_schema = '<schema>'
  AND table_name = '<table>';

This is the first important constraint: GROUP_CONCAT is only as complete as MySQL’s group_concat_max_len. For wide tables, the generated string can be truncated unless you raise that limit.

To generate a full statement, combine the pieces with CONCAT:

SELECT CONCAT(
               'SELECT ',
               GROUP_CONCAT(CONCAT('`', REPLACE(column_name, '`', '``'), '`')
                   ORDER BY ordinal_position SEPARATOR ', '),
               ' FROM `', REPLACE(table_schema, '`', '``'), '`.`', REPLACE(table_name, '`', '``'), '`;'
       ) AS solution
FROM information_schema.COLUMNS
WHERE table_schema = '<schema>'
  AND table_name = '<table>';

Notice the backticks: identifiers should be quoted, not just concatenated. That protects you from reserved words, spaces, and special characters in names. The REPLACE(..., '`', '``') calls handle the rare but real case where an identifier itself contains a backtick.

Why this works

The invariant is: every row in information_schema.COLUMNS corresponds to one physical column in the target table. Once you filter by schema and table, the query becomes a reliable source of truth for code generation. The main failure modes are predictable: