Locating Columns that Contain a String in their Name

Question

Other than manually traversing every table schema in the entire database, how can I produce a list of all tables that contain a field containing the string "email" in Pervasive 13?

For example, in IBM DB2, I can do this with a query like this:

select tabschema,tabname,colname
from syscat.columns
where upper(colname) LIKE UPPER('%email%')
order by tabname

How can I achieve this in Pervasive 13?

Answer 1

You can query the System Objects, use:

SELECT f.Xf$Name, g.Xe$Name
FROM X$File f
INNER JOIN X$Field g ON g.Xe$File = f.Xf$Id
WHERE UPPER(g.Xe$Name) LIKE '%EMAIL%';

Answer 2

I'm still open to other suggestions, but the way I did this was by exporting the database schema to a .sql text file, and I used a regular expression create table.*email to search through that file and locate all the tables containing a column with email in their name.

This worked, but I look forward to other people's suggestions.