(Music)
Hello and welcome.
In this video,
we'll look at how to get information about
tables and their columns in a database.
Now how would you get a list of tables in the database?
Sometimes your database may contain several tables,
and you may not remember the correct name.
For example, you may wonder
whether the table is called dog,
dogs or four legged mammals.
Database systems typically contain
system or catalog tables,
from where you can query
the list of tables and get their properties.
In DB2 this catalog is called syscat tables.
In SQL Server, it's
information schema tables, and in Oracle
it's all tables or user tables.
To get a list of tables in a DB2 database,
you can run the following query.
Select star from syscat tables.
This select statement will return
too many tables including system tables,
so it's better to filter the result as shown here.
Select tabschema, tabname,
create underscore time from syscat tables,
where tabschema equals ABC12345.
Please ensure that you replace
ABC12345 with your own DB2 username.
When you do a select star from syscat tables,
you get all the properties of the tables.
Sometimes we're interested in
specific properties such as creation time.
Let's say you've created
several tables with similar names.
For example, dog one,
dog underscore test, dog test one and so on. But,
you want to check which of these tables was
the last one you created; to do so,
you can issue a query like select tabschema,
tabname, create underscore time from syscat tables:
Where a tabschema equals QCM54853.
The output will contain the schema name,
table name, and creation time
for all tables in your schema.
Next, let's talk about how to
get a list of columns in a table.
If you can't recall
the exact name of a column for example,
whether it had any lowercase characters
or an underscore in its name,
in DB2 you can issue a query like the one shown here.
Select star from syscat columns
where tab name equals dogs.
For your information, in my SQL,
you can simply run the command show columns from dogs,
or you may want to know
specific properties like the datatype
and length of the datatype.
In DB2, you can issue
a statement like, select distinct name,
coltype, length from sysibm,
syscolumns where tbname equals dogs.
Here we look at the results
of retrieving column properties,
for a real table called
Chicago Crime Data from a Jupyter notebook.
Notice in the output,
you can tell certain column names show different cases.
For example, the column titled arrest has an uppercase A,
and the rest of the characters are lowercase.
So, keep in mind that when you
refer to this column in your query,
not only must you enclose
the word arrest within double quotes,
you must also preserve
the correct case inside the quotes.
In this video, we saw how to
retrieve table and column information.
Thanks for watching.
(Music)