Learn through the super-clean Baeldung Pro experience:
>> Membership and Baeldung Pro.
No ads, dark-mode and 6 months free of IntelliJ Idea Ultimate to start with.
1. Introduction
Querying a database table’s metadata is often useful, especially when using a new schema. In modern databases, for example, we can retrieve a table’s column names, data types, constraints, and indexes.
In this tutorial, we’ll explore obtaining a table’s description in PostgreSQL, MySQL, and SQL Server. The examples will be based on the University schema.
2. Using the Information_Schema
The information_schema schema is an internal SQL schema that contains read-only views of database metadata, such as table and column names, definitions, and procedures.
For our case, we’ll use the Columns view. Although the syntax is almost the same for the most popular databases, they also have some minor differences, so we’ll cover PostgreSQL, MySQL, and SQL Server separately.
2.1. PostgreSQL
For PostgreSQL, we need to lowercase the table name:
SELECT table_catalog, table_schema, table_name,
column_name, column_default, is_nullable,
data_type, is_updatable
FROM information_schema.columns
WHERE table_name = 'course';And in our University schema, we see Course‘s six columns:
|table_catalog|table_schema|table_name|column_name |column_default|is_nullable|data_type |is_updatable|
|-------------|------------|----------|-------------|--------------|-----------|-----------------|------------|
|University |public |course |credits | |YES |integer |YES |
|University |public |course |department_id| |YES |integer |YES |
|University |public |course |id | |NO |character varying|YES |
|University |public |course |name | |YES |character varying|YES |
|University |public |course |textbook | |YES |character varying|YES |
|University |public |course |is_active | |YES |character varying|YES |
So, we have the full description of the table. More specifically, we have the table’s schema; each row holds information about each column. We can see the column name, whether there is a default value, whether nulls are allowed, the data type of each column, and whether it’s updatable.
2.2. MySQL
In MySQL, we can use the table’s name as it’s defined, without lowercasing the first letter:
SELECT table_schema, table_name, column_name, column_default,
is_nullable, data_type, column_type, privileges
FROM information_schema.columns
WHERE table_name = 'Course';The results differ a bit as well:
|TABLE_SCHEMA|TABLE_NAME|COLUMN_NAME |COLUMN_DEFAULT|IS_NULLABLE|DATA_TYPE|COLUMN_TYPE |PRIVILEGES |
|------------|----------|-------------|--------------|-----------|---------|------------|-------------------------------|
|University |Course |id | |NO |varchar |varchar(10) |select,insert,update,references|
|University |Course |name | |YES |varchar |varchar(60) |select,insert,update,references|
|University |Course |textbook | |YES |varchar |varchar(100)|select,insert,update,references|
|University |Course |credits | |YES |int |int |select,insert,update,references|
|University |Course |is_active | |YES |varchar |varchar(10) |select,insert,update,references|
|University |Course |department_id| |YES |int |int |select,insert,update,references|
Now, we have the exact column type definition in the column_type column.
Here, we selected almost the same columns as in PostgreSQL. However, because MySQL holds different metadata, we don’t have the is_updatable column, but we have the privileges column. It contains information about the actions we can perform on each column, like SELECT, UPDATE, and INSERT.
2.3. SQL Server
SQL Server holds a bit less information than the other databases. Still, the query is very similar:
SELECT table_catatog, table_schema, table_name, column_name,
column_default, is_nullable, data_type
FROM information_schema.columns
WHERE table_name = 'Course';As we can see, we don’t have a column for the actions we can perform in each column. The results, however, follow the same structure as in the other cases:
|table_catalog|table_schema|table_name|column_name |column_default|is_nullable|data_type|
|-------------|------------|----------|-------------|--------------|-----------|---------|
|University |dbo |Course |id | |NO |varchar |
|University |dbo |Course |name | |YES |varchar |
|University |dbo |Course |textbook | |YES |varchar |
|University |dbo |Course |credits | |YES |int |
|University |dbo |Course |is_active | |YES |varchar |
|University |dbo |Course |department_id| |YES |int |
3. Database Specific Commands
In addition to the information_schema schema, which is available in most SQL databases, we can also use database-specific commands.
3.1. PostgreSQL
In PostgreSQL, we can use psql, PostgreSQL’s interactive terminal. After connecting to our database, we can use the \d+ command to get a table’s description in the terminal:
\d+ Coursewhich gives us:
Table "public.course"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | character varying(10) | | not null | | extended | | |
name | character varying(60) | | | | extended | | |
textbook | character varying(100) | | | | extended | | |
credits | integer | | | | plain | | |
is_active | character varying(10) | | | | extended | | |
department_id | integer | | | | plain | | |
Indexes:
"course_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"course_department_id_fkey" FOREIGN KEY (department_id) REFERENCES department(id)
Access method: heap
In addition to what we had before, we also get foreign-key constraints and indexes.
3.2. MySQL
If we use MySQL’s describe command:
describe Course;We get a rather terse description:
|Field |Type |Null|Key|Default|Extra|
|-------------|------------|----|---|-------|-----|
|id |varchar(10) |NO |PRI| | |
|name |varchar(60) |YES | | | |
|textbook |varchar(100)|YES | | | |
|credits |int |YES | | | |
|is_active |varchar(10) |YES | | | |
|department_id|int |YES |MUL| | |
Again, we have retrieved the column names, types, nullability info, primary and foreign keys, and default values, if any.
3.3. SQL Server
We can use built-in stored procedures in SQL Server to get information about a table. The most useful one is the sp_help procedure:
exec sp_columns Course;This will give us a lot of useful outputs, such as table name and creation date:
|Name |Owner|Type |Created_datetime |
|------|-----|----------|-----------------------|
|Course|dbo |user table|2025-04-01 10:57:03.057|
In the next snippet, we have the column definitions and type-specific configuration per column:
|Column_name |Type |Computed|Length|Prec |Scale|Nullable|TrimTrailingBlanks|FixedLenNullInSource|Collation |
|-------------|-------|--------|------|-----|-----|--------|------------------|--------------------|----------------------------|
|id |varchar|no |10 | | |no |no |no |SQL_Latin1_General_CP1_CI_AS|
|name |varchar|no |60 | | |yes |no |yes |SQL_Latin1_General_CP1_CI_AS|
|textbook |varchar|no |100 | | |yes |no |yes |SQL_Latin1_General_CP1_CI_AS|
|credits |int |no |4 |10 |0 |yes |(n/a) |(n/a) | |
|is_active |varchar|no |10 | | |yes |no |yes |SQL_Latin1_General_CP1_CI_AS|
|department_id|int |no |4 |10 |0 |yes |(n/a) |(n/a) | |
Then, we can see an index for the primary key and a second unique index for the same column:
|index_name |index_description |index_keys|
|----------------------------|---------------------------------------------------|----------|
|PK__Course__3213E83F0B9138C6|clustered, unique, primary key located on PRIMARY |id |
|UQ__Course__3213E83E088E859C|nonclustered, unique, unique key located on PRIMARY|id |
Finally, we have information about the table’s constraints:
|constraint_type |constraint_name |delete_action|update_action|status_enabled|status_for_replication|constraint_keys |
|-----------------------|----------------------------|-------------|-------------|--------------|----------------------|-----------------------------------------|
|FOREIGN KEY |course_department_id_fkey |No Action |No Action |Enabled |Is_For_Replication |department_id |
| | | | | | |REFERENCES University.dbo.Department (id)|
|PRIMARY KEY (clustered)|PK__Course__3213E83F0B9138C6|(n/a) |(n/a) |(n/a) |(n/a) |id |
|UNIQUE (non-clustered) |UQ__Course__3213E83E088E859C|(n/a) |(n/a) |(n/a) |(n/a) |id |
We can see a foreign key, the primary key, and a unique constraint.
4. Conclusion
In this article, we learned how to get a table’s description using the information_schema schema and the Columns view. The queries are similar but not the same in PostgreSQL, MySQL, and SQL Server because the view differs in these database systems.
We also used specific commands per database for the same result.
As always, the sample queries used in this article are available over on GitHub.