Describe table structure

sql

information-schema

sql-queries

database-structure

byAlex Kataev·Oct 23, 2024

Quickly fetch your table's structure with:

MySQL:
```
DESCRIBE table_name;
```
SQL Server:
```
EXEC sp_help 'table_name';
```
PostgreSQL (inside psql):
```
\d table_name
```
SQLite:
```
.schema table_name
```

Each query provides table columns, data types, and other attributes as output. Swap out table_name with your table's name.

The anatomy of SQL Tables

To inspect a table's structure, use INFORMATION_SCHEMA, SQL-standard compliant and existing in most SQL databases.

Fetching column details through INFORMATION_SCHEMA

--Who knew columns had so much to say! 🗣️
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name';

Replace 'table_name' with your target's name. This command divulges column names, data types, their nullability, and default values.

Key considerations

Permissions: Confirm if you have the required rights to query system tables or views.
Database Variants: Some databases contain proprietary extensions that may alter or extend INFORMATION_SCHEMA —always verify with the documentation.
Performance: Be mindful that if your schema is large, INFORMATION_SCHEMA might be slow. In these cases, database-specific commands may perform better.

Essential developer skills

Aliases: Use aliases to abbreviate complex column names within lengthy SQL queries.
Type casting: Occasionally, you'd have to perform type-casting to present data in the required format.
Computed columns: SQL Server provides computed columns━define and utilise them to improve performance.

Detailing constraints and indexes

Tables are more than columns and data types; constraints and indexes play key roles in ensuring data integrity and performance:

-- Constraints? More like the fun police of your database! 🚨
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME = 'table_name';

-- The fast lane of any database! 🏎️
SELECT * FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_NAME = 'table_name';