EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials DB2 Tutorial DB2 LIST TABLES
Secondary Sidebar
DB2 Tutorial
  • DB2 Tutorial
    • What is DB2?
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE

DB2 LIST TABLES

DB2 LIST TABLES

Introduction to DB2 List Tables

The following article provides an outline for DB2 LIST TABLES. When database is designed, we need many tables to store the data related to different objects. After the data is being maintained, stored, updated and retrieved during the usage, there might be a need that we have to sometimes check the structure of the database and the number of the tables present in the database along with some description about all the tables. Also, there might be a need to modify or restructure the database based on certain changed requirement.

In all such cases, we can see the list of all the tables present inside a particular database in DB2 by using one existing internal schema named syscat. Here, we will see about how we can make the use of syscat scheme in order to list out all the tables present inside the database. We will also see how we can use the LIST TABLES command to quickly get the reference of all the system tables present inside the database.

Prerequisite

In order to make the use of the LIST TABLES command, a user should first connect to the database and should have one of the following privileges with him/her.

  • Control privileges over the entities of the database
  • DBADM
  • SYSMAINT
  • SELECTIN privilege on the schema named SYSCAT, which helps to know the system catalog information
  • SYSMON
  • SYSCTRL

First, one should initialize the database connection with the system before listing all the tables inside the database. The establishment of the database connection with the default database automatically exists if we have enabled implicit connection.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax of DB2 LIST TABLES

The syntax of the LIST TABLES command is given below:

LIST TABLES [FOR USER/ALL/SCHEMA name of schema/SYSTEM SHOW DETAIL];

In the above-mentioned syntax, the use of FOR clause is optional, and this clause helps us to retrieve the data for a specific scenario like for all the users or only the current user of can be for a particular schema when we have to retrieve the list of all the tables for a schema other than the default or selected one or FOR clause can also help us to list all the system tables present inside the DB2 RDBMS.

The SHOW DETAIL clause is also optional and can be used to display and retrieve the full names of the database, schemas and the tables. In case if we don’t specify this clause, then the names of the table that are retrieved are in the truncated format, which is upto 30 characters, and the name of the schema that are retrieved are truncated upto characters, and the > symbol specification represents the 31st column in the name of the table while 15th column in the name of the schema.

Alternatively, we can also make the use of the SELECT query statement to list out all the tables from the schema named SYSCAT, which stores the details of all the tables inside the table named tables in it.

Example of DB2 LIST TABLES

Given below is the example of DB2 LIST TABLES:

Let us try to retrieve all the tables inside the schema using the list tables command firstly.

Code:

LIST TABLES;

The execution of the above query statement gives out the following output, which is present in our DB2 database. However, the results may vary depending on the contents and structure of your database.

Output:

DB2 List Tables 1

As we can observe that the list tables command provides us with the details of all the names of the tables that exist in our database and also provides the details regarding the name of the schema to which the table belongs, type of the table and the time when the table was created. Our database consists of only three tables in it, namely, STORE_LOCATIONS, CUSTOMERS, and SALES. Hence, only three records are retrieved in the result set.

Alternatively, when we make the use of the SYSCAT schema to get the details of the tables by using the SELECT query statement, then our query statement looks like the following.

Code:

select tabname as "Name of the table", tabschema as "Name of Schema", tbspace as "Table Space" from syscat.tables;

The execution of the above query statement gives out the following output, which is present in our DB2 database. However, the results may vary depending on the contents and structure of your database.

Output:

DB2 List Tables 2

Before we go for executing any of the above two query statements to list of the tables inside DB2, it is necessary that we first establish the connection with the database using connect to <name of the database to connect>.

For example, suppose that we have a database whose name is store_details, then my connection query statement will become as shown below.

Code:

connect to store_details

Output:

The execution of the above query statement gives out the following output, which is present in our DB2 database.

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,171 ratings)

Database Connection Information

Conclusion

The LIST TABLES command can be used in DB2 to get the list of all the tables that are present in our database. But before we do that, it is necessary that we have certain privileges with us that are mentioned in the prerequisites section. Any one of these privileges will allow you to use this query statement. We also need to connect to the database whose tables we wish to list out before using the LIST TABLES command. Alternatively, we can also make the use of the SELECT query statement to retrieve the names of the tables inside a particular database from the SYSCAT schema in which table named tables stores the details of all the tables present in the database.

Making the use of the SELECT statement also allows us to filter out the data of the table names using the where clause. We can also retrieve the sorted list that is ordered on the basis of a certain column value, which can be either schema name or the name of the tables. We can also change the details that we are trying to retrieve by specifying the columns that we want and the order in which we want to retrieve the list of the tables.

Recommended Articles

This is a guide to DB2 LIST TABLES. Here we discuss the introduction and example of DB2 LIST TABLES for better understanding. You may also have a look at the following articles to learn more –

  1. What is DB2?
  2. Database Management Software
  3. Data Analysis Tools
  4. Data Modelling Tools
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more