How to search all tables for specific column in Intersystems Cache database

Primary tabs

Is there a way to query the database structure?  In SSMS there are queries for finding tables with a column with a certain name (using LIKE).  And there is the redgate tool SQL Search.  But I'm not sure how to go about looking for columns that have say a value of 'PATID' and returning all tables that match.  Does anyone know?

Replies

Something like this may work:

SELECT parent FROM %Dictionary.CompiledProperty where name like 'Name' and NOT %ID %Startswith '%'

This will give you all tables that aren't % classes that have a "Name" property

It says I'm not priveleged for this operation.  But thanks for letting me know.

indeed, system schemas (whose name starts with a % sign) require certain privileges (that you quickly get used to once you have them :-) ). That's why I suggested querying the INFORMATION_SCHEMA

and if you prefer something more database-independent, you can use the standard INFORMATION_SCHEMA package:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'PATID%'