Skip to content
Psila.AI

INFORMATION SCHEMA

ScramDB provides compatibility with the standard SQL information_schema. This schema contains views that allow you to query metadata about the database objects themselves, such as tables and columns.

This is useful for introspection, understanding the database structure, and for tools that rely on this standard schema.

Supported Views

Currently, the following information_schema views are implemented:

  • information_schema.tables: Lists all tables (both internal and external) accessible within the current database/catalog.
  • information_schema.columns: Lists all columns for all accessible tables.

(Support for other views like schemata, views, etc., may be added in the future).

Querying Examples

List All Tables

SELECT table_catalog, table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'; -- Or filter by other schemas if created
  • table_catalog: The catalog the table belongs to (often the database name).
  • table_schema: The schema the table belongs to (e.g., public).
  • table_name: The name of the table.
  • table_type: Indicates the type of table, typically BASE TABLE for regular tables (internal/external) or VIEW for views.

List Columns for a Specific Table

SELECT column_name, data_type, is_nullable, ordinal_position
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'users';
  • column_name: The name of the column.
  • data_type: The data type of the column (e.g., bigint, varchar, timestamp).
  • is_nullable: YES if the column allows NULL values, NO otherwise.
  • ordinal_position: The numeric position of the column within the table definition (starting from 1).

Find All Tables with a Specific Column Name

SELECT table_schema, table_name
FROM information_schema.columns
WHERE column_name = 'customer_id';

You can use standard SELECT statement features (like WHERE, ORDER BY, LIKE) to filter and sort the metadata retrieved from these views.