pervasivepervasive-sql

Locating Columns that Contain a String in their Name

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?

Solution

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