SQL procedure to get a quick system catalog of files, tables and views

sql_syscat.sql

-- SQL procedure to get a quick system catalog of files, tables and views.
-- I use this from ACS all the time to get a catalog overview.
--
-- Simply paste this gist into ACS SQL and select "run all" 
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2020
-------------------------------------------------------------------------------------
create or replace procedure  qusrsys.syscat  (
	  in lib   varchar(20) default '',
	  in file  varchar(10) default '',
	  in table varchar(32) default '',
	  in text  varchar(32) default ''
)
language sql 
dynamic result sets 1

begin

    declare c1 cursor with return for
    select 
        table_schema as lib,
        system_table_name as file,
        table_name as table,
        table_text as text ,
        table_type, file_type
    from  systables 
    where table_schema like '%' concat upper(lib) concat '%'
    and   system_table_name like '%' concat upper(file) concat '%'
    and   table_name  like '%' concat upper(table) concat '%'
    and   lower(table_text)    like '%' concat lower(text) concat '%'
    order by 1, 2; 
    
    open c1;

end; 

-- Usecases:

-- Get list of all files, tables and views in all libraries and schemas
call syscat ();


-- Get list of all files, tables and views in library with names like QIWS
-- Note it is not case sensistive and makes a generic search
call syscat (lib=>'qiws');

-- Get list of all files, tables and views in library (schema) with 
-- names like QIWS and table name contains 'cust' somwhere 
-- Note it is not case sensistive and makes a generic search
call syscat (lib=>'qiws', table  =>'cust');

-- Get list of all files, tables and views in library (schema) with 
-- names like QIWS and description contains 'cust' somwhere 
-- Note it is not case sensistive and makes a generic search
call syscat (lib=>'qiws', text =>'cust');