How to check a column exists in a table in Intersystems Cache SQL?

Question

I've noticed that while I can use %dictionary.compiledclass to get a table with schema names and table names, which allow querying for their existence, I cannot do the same for columns. I have yet to find the command that allows verifying whether a column exists or not, or retrieving numerous column names using LIKE "prefix%".

Is there even such a thing? Or an alternative?

Answer 1

You can use %Dictionary.CompiledProperty table and SqlFieldName column of that table. For example to find out tables that have column 'ColumnName' you can use this query:

select parent->SqlTableName 
from %dictionary.compiledproperty 
where SqlFieldName='ColumnName'

Answer 2

Execute this Query : select * from %dictionary.compiledproperty Where parent='TableName' and SqlFieldName='ColumnName' Check Row Count value ,0 not exist