Latest Enterprise DNA Initiatives


Table and column Metadata

In a course of development I may change the source for a query multiple times. I have started always putting a remove other columns as my last step in the Query editor to try to minimalise the impact of changes.

How do I know when I have the old query and the new that the column names and data types are the same? I guiess also some sort of sense check of the data may be a follow up question.

So in SQL Server I would use sysobjects and syscolumns to give me a list of columns and how they are defined for a table and then compare the column names and data type definitions.

I have tried %SYSTEM.DBSCHHEMA_COLUMNS but this only appears to give a list of column names

Thanks
E

Hi @ells

Try $system.DISCOVER_STORAGE_TABLE_COLUMNS

SELECT dimension_name, attribute_name, DataType            
FROM $system.DISCOVER_STORAGE_TABLE_COLUMNS
WHERE dimension_name = 'YOUR TABLE NAME'
AND COLUMN_TYPE = 'BASIC_DATA'

@jbressan
Aswesome,
just what I was looking for.
Many Thanks
E

1 Like