SQL Describe Table (In Different Vendors)

In SQL, you may need to find out more about the table and its columns. This is often called “sql describe table” or describing a table.

Different vendors (Oracle, SQL Server, MySQL, PostgreSQL) have different methods for letting you see this information.

In this post, you’ll learn how to see the table details using the DESCRIBE command, or whatever the method is for each database vendor.

Table of Contents

Summary

Here’s a summary of the different methods you can use on each database:

Oracle: Describe command
SQL Server: sp_help procedure, sp_columns procedure, select from information schema
MySQL: Describe command, show columns command
PostgreSQL: \d command, select from information schema

Oracle

In Oracle, to describe a table we use the DESCRIBE command.

Describe Command

This is what the DESCRIBE command looks like:

DESCRIBE {table_name | view_name}

This can be run at a command prompt (like SQL*Plus or sqlcl) or in SQL Developer.

It can be used on tables or on views.

Here’s an example on a table called “customer”:

DESCRIBE customer;

This is the output:

Name        Null?    Type          
----------- -------- ------------- 
CUSTOMER_ID NOT NULL NUMBER        
FIRST_NAME           VARCHAR2(200) 
LAST_NAME            VARCHAR2(200) 
EMAIL                VARCHAR2(350)

We can see the column names of each column in the table, whether it has a NOT NULL constraint, and the data type.

We could alternatively run the DESC command, as DESC is the same as DESCRIBE.

DESC customer;

This is the output:

Name        Null?    Type          
----------- -------- ------------- 
CUSTOMER_ID NOT NULL NUMBER        
FIRST_NAME           VARCHAR2(200) 
LAST_NAME            VARCHAR2(200) 
EMAIL                VARCHAR2(350)

So, in Oracle, the DESCRIBE or DESC command can be used.

SQL Server

There is no DESCRIBE command in SQL Server.

However, there are two methods you can use to find similar information.

Use the sp_help procedure

You can call the sp_help procedure to see information about a table.

EXEC sp_help tablename;

For example, we can run it on the customer table:

EXEC sp_help customer;

This shows quite a few result sets:

Name	Owner	Type	Created_datetime
customer	dbo	user table	2021-09-13 0:24:11

Column_name	Type	Computed	Length	Prec	Scale	Nullable	TrimTrailingBlanks	FixedLenNullInSource	Collation
customer_id	int	no	4	10	0	no	(n/a)	(n/a)	NULL
first_name	varchar	no	200			yes	no	yes	SQL_Latin1_General_CP1_CI_AS
last_name	varchar	no	200			yes	no	yes	SQL_Latin1_General_CP1_CI_AS
email	varchar	no	350			yes	no	yes	SQL_Latin1_General_CP1_CI_AS

Identity	Seed	Increment	Not For Replication
No identity column defined.	NULL	NULL	NULL

RowGuidCol

No rowguidcol column defined.

Data_located_on_filegroup

PRIMARY

There’s a lot of information here which is helpful and more than just the columns.

Use sp_columns

Another method is to use the sp_columns procedure.

EXEC sp_columns tablename;

Our example with the customer table looks like this:

EXEC sp_columns customer;

Here’s the output.

TABLE_QUALIFIER

TABLE_OWNER

TABLE_NAME

COLUMN_NAME

DATA_TYPE

TYPE_NAME

PRECISION

LENGTH

SCALE

RADIX

NULLABLE

REMARKS

COLUMN_DEF

SQL_DATA_TYPE

SQL_DATETIME_SUB

CHAR_OCTET_LENGTH

ORDINAL_POSITION

IS_NULLABLE

SS_DATA_TYPE

test_dupes

dbo

customer

customer_id

int

NULL

test_dupes

dbo

customer

first_name

varchar

200

NULL

200

YES

test_dupes

dbo

customer

last_name

varchar

200

NULL

200

YES

test_dupes

dbo

customer

varchar

350

NULL

350

YES

We can see the column names and a range of data about the columns.

Select from Information Schema

The third method to generate an SQL Server describe table output is to query the information schema.

We query information_schema.columns and filter on our table name.

Here’s the query for the customer table:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'customer';

The output is:

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

COLUMN_NAME

ORDINAL_POSITION

COLUMN_DEFAULT

IS_NULLABLE

DATA_TYPE

CHARACTER_MAXIMUM_LENGTH

CHARACTER_OCTET_LENGTH

NUMERIC_PRECISION

NUMERIC_PRECISION_RADIX

NUMERIC_SCALE

DATETIME_PRECISION

CHARACTER_SET_CATALOG

CHARACTER_SET_SCHEMA

CHARACTER_SET_NAME

COLLATION_CATALOG

COLLATION_SCHEMA

COLLATION_NAME

DOMAIN_CATALOG

DOMAIN_SCHEMA

DOMAIN_NAME

test_dupes

dbo

customer

customer_id

NULL

int

NULL

test_dupes

dbo

customer

first_name

NULL

YES

varchar

200

NULL

iso_1

NULL

SQL_Latin1_General_CP1_CI_AS

NULL

test_dupes

dbo

customer

last_name

NULL

YES

varchar

200

NULL

iso_1

NULL

SQL_Latin1_General_CP1_CI_AS

NULL

test_dupes

dbo

customer

NULL

YES

varchar

350

NULL

iso_1

NULL

SQL_Latin1_General_CP1_CI_AS

NULL

The output is very similar to the sp_columns procedure but shows a bit more information.

You may want to test each method to see which is faster. You may also prefer the information_schema method if you want to join it to other tables or you want a single result set.

So, that’s how you can describe a table in SQL Server:

Use the sp_help procedure
Use the sp_columns procedure
Select from information_schema.columns

MySQL

In MySQL, there are two methods to describe a table: the DESCRIBE command or the SHOW COLUMNS command.

DESCRIBE Command

We can use the DESCRIBE command to describe a table in MySQL.

DESCRIBE tablename;

Here’s an example using the customer table:

DESCRIBE customer;

The output shows:

Field	Type	Null	Key	Default	Extra
customer_id	int	YES		(null)
first_name	varchar(200)	YES		(null)
last_name	varchar(200)	YES		(null)
email	varchar(350)	YES		(null)

We can see the column names, data types, and more.

SHOW COLUMNS Command

Another approach is to use the SHOW COLUMNS command:

SHOW COLUMNS FROM tablename;

Using our example customer table, it looks like this:

SHOW COLUMNS FROM customer;

Our output is the same as the DESCRIBE command:

Field	Type	Null	Key	Default	Extra
customer_id	int	YES		(null)
first_name	varchar(200)	YES		(null)
last_name	varchar(200)	YES		(null)
email	varchar(350)	YES		(null)

This is an alternative to using the describe command.

PostgreSQL

There are a couple of ways to describe a table in PostgreSQL.

Run the \d command

The \d command is a shorthand for describing an object in PostgreSQL.

To show a simple description of the table, run:

\d tablename

Or, to show a more detailed view of the table:

\d+ tablename

These can work well. However, they only work in the command line.

If you’re using an IDE such as DBeaver or DataGrip, these won’t work.

There is another method.

Select from Information Schema

Another way to describe a table in PostgreSQL is to select from the information schema.

We can run a query like this to see all info for columns in the customer table.

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'customer';

There are a lot of columns shown in this output, so you can restrict it by selecting only a few columns:

SELECT
column_name,
data_type,
character_maximum_length,
column_default,
is_nullable,
numeric_precision
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'customer';

The output is a bit more manageable:

column_name	data_type	character_maximum_length	column_default	is_nullable	numeric_precision
customer_id	integer	(null)		YES	32
first_name	character varying	200		YES	(null)
last_name	character varying	200		YES	(null)
email	character varying	350		YES	(null)

So that’s how you can describe a table in PostgreSQL.

Conclusion

The SQL describe table command can be run on some databases, and there are alternative commands to run it on other databases.

Summary

Oracle

Describe Command

SQL Server

Use the sp_help procedure

Use sp_columns

Select from Information Schema

MySQL

DESCRIBE Command

SHOW COLUMNS Command

PostgreSQL

Run the \d command

Select from Information Schema

Conclusion

Leave a Comment Cancel Reply