Redshift SHOW TABLE Command

Have you ever heard about the Redshift SHOW TABLE command to view the definition of the table? If not, then read this article to find out what SHOW TABLE command is and how it can be used to get the description of the database table. In Amazon Redshift, the SHOW TABLE command is used to get the description or the definition of your database table. It displays all the columns with other attributes like data type of each column, present in the table. In this blog, we will discuss how we can use the SHOW TABLE command to get the definition of a database table.

Redshift overview

Amazon Redshift is a fully serverless data warehousing server provided by AWS. It is an advanced professional and industrial level tool to carry out big data jobs and data analytics. It can utilize parallel nodes to increase its computing power which will help to resolve complex queries and tasks.

The syntax for the SHOW TABLE command

The syntax to use the SHOW TABLE command in Redshift is as follows:

SHOW TABLE <schema name>.<table name>

The shema name is the database schema in which the desired table exists for which you want to find the details.

Similarly, the table name field specifies the table name in the specified schema for which you want to get the description or definition.

Using the SHOW TABLE command

In this section, we are going to see how to use the SHOW TABLE command in Redshift with hands-on practical examples to make things more clear and more understandable.

Finding table definitions and columns

Suppose you are working on a database of your company that is built using Amazon Redshift and you want to find out all the columns present in the admin_team table. For this purpose, you can use the following Redshift query which will result in all the columns of the table.

show table organization.admin_team

We can see in the results output that the table admin_team has just two columns named id having integer data type with azr64 encoding and the other column named name with varchar data-type and lzo encoding. Further, the distribution style of the table is set to auto which is a key-based distribution style.

Besides, showing the definition of the Redshift table, the SHOW TABLE command also returns the original command to create the new table with the same definition.

Creating a new table using the old definition

Now, take an example where you are going to expand your organization and add a new database table for the new software development department for which you have to create a new table named dev_team. To keep all the database tables with the same pattern, you will need to look at some previous data as it is not usually possible to remember all the details in mind. For this, you just need to use the SHOW TABLE command to get the definition of any similar column.

Suppose you want to build the new table using the web_team table definition. For this, get the definition of the source table i.e web_team using the SHOW TABLE command.

Show table organization.web_team

We just have to edit the table name in the output and all the definitions will just remain the same.

You can see how easily we have created our new database table for our dev_team using the old definition of the web_team table just with the help of the Redshift SHOW TABLE command.

Conclusion

The SHOW TABLE command in Redshift is very useful if you want to look into the detailed schema of a table in Redshift. It tells you about all the columns in the database with their data type and encoding plus also the Redshift distribution style for that table. The output of this command is often useful if you are going to create a similar table having the same columns and data type. You can simply take the definition of any table and then create a new table from it.