SQL Database Schema and Metadata: Inspect Tables and Columns

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

The word schema has two common meanings. It can describe the overall design of tables, columns, keys and relationships. In PostgreSQL and SQL Server it also names a namespace that contains objects, so public.sales or dbo.sales is a qualified object name. MySQL commonly uses schema as a synonym for database. SQLite uses attached database names such as main and temp rather than the same server namespace model.

INFORMATION_SCHEMA supplies standard-style views in PostgreSQL, MySQL and SQL Server for common metadata such as tables and columns. Filter by the relevant catalog and schema as well as the table name to avoid combining objects with the same name from different namespaces. Metadata visibility depends on permissions; an absent row can mean the current account cannot see the object, not necessarily that the object does not exist.

Engine-specific catalogs expose details beyond standard views. PostgreSQL has pg_catalog, SQL Server has sys views, MySQL has its own catalog and information-schema extensions, and SQLite stores schema definitions in sqlite_schema and offers PRAGMA inspection commands. SQLite PRAGMA table_info returns common column details, while table_xinfo includes additional hidden or generated-column information. Catalog fields and meanings must be interpreted through the engine’s documentation.

Inspect types, nullability, default expressions and key definitions separately. A column named customer_id does not prove a foreign key exists, and a column named id does not prove it is a primary key. Reading a raw CREATE statement can help, but automated tooling should use reliable catalog information rather than fragile text matching. Metadata is not the same as business data: table definitions cannot tell you whether every saved email is valid. Combine schema inspection with targeted data-quality queries when validating a migration or import.

This PRAGMA is SQLite-specific. In PostgreSQL, MySQL and SQL Server, query INFORMATION_SCHEMA.COLUMNS with table_name and the appropriate schema filter. PostgreSQL and SQL Server namespace semantics differ from MySQL’s database/schema equivalence. Use engine catalogs for details that INFORMATION_SCHEMA does not expose reliably.

Check the engine-specific syntax and version requirements in SQLite table_info metadata, PostgreSQL information schema.

In SQLite, list the CREATE definitions of the two main practice tables using sqlite_schema. Restrict the result to ordinary tables and sort by name.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT name,sql FROM sqlite_schema
WHERE type='table' AND name IN ('customers','sales') ORDER BY name;

name | sql
customers | The CREATE TABLE customers definition from setup
sales | The CREATE TABLE sales definition from setup

The rows contain the saved schema SQL for the two known objects, not their business records. The complete sql strings correspond to the setup definitions above; formatting may reflect the original creation text. The type and name filters exclude indexes and unrelated temporary objects.

Compare PRAGMA table_info(customers) with the CREATE text in sqlite_schema and locate the primary-key declaration in both representations. Then inspect sales and note that customer_id has no enforced foreign key in this deliberately simple fixture. A matching name and valid-looking sample data are not substitutes for an actual relationship constraint.