Db2 Query to retrieve all table names for a given schema

Digging into SYSCAT.TABLES

SYSCAT.TABLES is pivotal for managing DB2. Think of it as the contact list on your phone, storing vital data about your friends (which, in our case, are tables). It furnishes us with table names, creation info, ownership details, and intriguingly, table types.

Let's take this for a spin and single out user-created tables only:

SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'YOUR_SCHEMA' AND TYPE = 'T';


Now, our trusty query offers us just our tables. DB2's system tables, you are not invited to this party! 🎉💃

Query precision: deploying LIKE

At times, it's all about details. When you need to zero in on tables with certain name parts, LIKE comes to the rescue:

SELECT TABNAME FROM SYSCAT.TABLES
WHERE TABSCHEMA = 'YOUR_SCHEMA' AND TABNAME LIKE '%CUR%';


You've seen these % wildcards before, right? They'll fetch tables in your schema with 'CUR' anywhere in their names. Kind of like remembering someone's number from their last four digits. Super handy!

Let's talk platforms

DB2 spans across lots of platforms, and the differences can be significantly head-tilty. Let's zero in on IBM iSeries (AS/400):

SELECT TABLE_NAME FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = 'YOUR_SCHEMA' AND TABLE_TYPE = 'T';


Notice how we changed TABNAME and TYPE to TABLE_NAME and TABLE_TYPE? Wield your DB2 tools like a pro by considering platform-specific terminology.

Juggling platforms

Working with various DB2 environments, such as DB2 for z/OS and DB2 for Linux, UNIX, and Windows (LUW), can be like alternating between languages. The names and columns of catalog tables differ, so always make sure you know who you're speaking to. Adjust your knowledge like a seasoned translator!

A look at all tables

Handling a wider scope? Get the full table manifesto via the DB2 command line with db2 LIST TABLES FOR ALL. It's like peeking into everyone's closets, public and private, without needing a search warrant. Yes, you'd be a database detective!