Is there an SQL construct called COLUMNS() to return the number and type of columns in a table, or should schema introspection be done via INFORMATION_SCHEMA or database-specific catalog views?

Introduction / Context:Developers often need to inspect table metadata (column names, data types). SQL itself does not define a generic COLUMNS() function. This question clarifies the standard approach to schema introspection.

Given Data / Assumptions:

• We are working with relational databases (e.g., MySQL, PostgreSQL, SQL Server, Oracle).
• We want a portable way to discover column metadata.
• We must distinguish standard vs. vendor-specific methods.

Concept / Approach:ANSI SQL defines INFORMATION_SCHEMA views (e.g., INFORMATION_SCHEMA.COLUMNS) that expose metadata. Vendors also provide catalog views (e.g., sys.columns in SQL Server, ALL_TAB_COLUMNS in Oracle). There is no COLUMNS() aggregate or function in standard SQL to list or count columns directly.

Step-by-Step Solution:Query INFORMATION_SCHEMA.COLUMNS filtered by table_schema and table_name.COUNT() over that set yields the number of columns.Select data_type and ordinal_position for types and ordering.Use vendor catalog views if additional details are needed (collation, identity, computed).Wrap in a view or stored procedure for reuse.

Verification / Alternative check:Run queries against multiple DBMSs; INFORMATION_SCHEMA returns consistent metadata where implemented. No engine will accept COLUMNS() as a valid construct.

Why Other Options Are Wrong:Claims about ANSI or Oracle support for COLUMNS() are incorrect. The functionality belongs to metadata views, not an aggregate function.

Common Pitfalls:Relying on nonportable DESCRIBE commands; forgetting to filter by schema; conflating column count with column statistics.

Final Answer:Does not apply — there is no COLUMNS(); use INFORMATION_SCHEMA or catalog views