FB

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.

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 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 1 NO 56
test_dupes dbo customer first_name 12 varchar 200 200 NULL NULL 1 NULL NULL 12 NULL 200 2 YES 39
test_dupes dbo customer last_name 12 varchar 200 200 NULL NULL 1 NULL NULL 12 NULL 200 3 YES 39
test_dupes dbo customer email 12 varchar 350 350 NULL NULL 1 NULL NULL 12 NULL 350 4 YES 39

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 1 NULL NO int NULL NULL 10 10 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
test_dupes dbo customer first_name 2 NULL YES varchar 200 200 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL
test_dupes dbo customer last_name 3 NULL YES varchar 200 200 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL
test_dupes dbo customer email 4 NULL YES varchar 350 350 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL 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.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.