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?

score 1 · Accepted Answer · answered May 28 '22 at 18:28

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%';

answered May 28 '22 at 18:28

Luuk

12,245
5
22
33

Fantastic! How did you discover this? Works great! – Lonnie Best May 30 '22 at 04:53
1

The company I worked for used a Pervasive 7 database, and after that seen different version up to 13. The docs, i linked to, contain many thing which can help if the syntax in Pervasive/Actian is slightly different from other DBMS'es. – Luuk May 30 '22 at 17:19

score 0 · Answer 2 · answered May 13 '22 at 20:59

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.

Locating Columns that Contain a String in their Name

2 Answers2