How to search all tables for specific column in Intersystems Cache database
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?
Comments
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%'
Thank you! This worked well!