Search code examples
sqlintersystems-cache

What is the easiest way to search for user-defined columns in Intersystems Cache using SQL?

I am trying to search an extremely large Cache DB for columns that match a particular text string. In T-SQL, we could simply query the sys.columns view and then join it to sys.tables to get the name of all tables that have a column that matches the text we're looking for.

SELECT b.name AS table_name, a.name AS column_name
FROM sys.columns a
INNER JOIN sys.tables b ON a.object_id = b.object_id
WHERE b.type = 'U' AND a.name LIKE '%SEARCHTEXT%'

I've tried querying the %DICTIONARY.COMPILEDPROPERTY, but I haven't figured out how to limit the result set to user-defined classes. Is there a particular property that will limit the result set as desired? Perhaps a pattern on the class name?

Solution

  • This query:

    SELECT 
  parent AS class,
  parent->SqlQualifiedNameQ As table_name,
  name AS property
FROM %Dictionary.CompiledProperty 
WHERE NOT parent->id %STARTSWITH '%'

    Executed in every namespace beyond %SYS/SAMPLES/DOCBOOK would return user defined tables (system tables start with %, users should not use table names starting with %)