Categories:

System functions (Information)

SYSTEM$GET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT¶

Returns the list of columns that were set by a previous call to SYSTEM$SET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT for the specified Snowflake view (for example, for a specific ACCOUNT_USAGE, ORGANIZATION_USAGE, READER_ACCOUNT_USAGE, or INFORMATION_SCHEMA view) or for a built-in table function in one of these schemas.

For more information, see Handling new columns in SHOW command output, Snowflake views, and table functions.

See also:

SYSTEM$SET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT , SYSTEM$UNSET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT , SYSTEM$GET_ALL_DEFAULT_COLUMNS_OVERRIDES

Syntax¶

SYSTEM$GET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT(
  '<object_type>',
  '<database_name>',
  '<schema_name>',
  '<object_name>'
)

Arguments¶

'object_type'

Type of the object. Specify 'VIEW' for a Snowflake view or 'FUNCTION' for a built-in table function.

'database_name'

Name of the database that contains the object. For a view, specify 'SNOWFLAKE' or, for an INFORMATION_SCHEMA view, an empty string. For a table function, specify an empty string for an INFORMATION_SCHEMA table function, or 'SNOWFLAKE' for an ACCOUNT_USAGE, ORGANIZATION_USAGE, or READER_ACCOUNT_USAGE table function.

'schema_name'

Name of the schema that contains the object. For a view, specify the name of a schema in the SNOWFLAKE database or 'INFORMATION_SCHEMA'. For a table function, specify the name of the schema that contains it, such as 'INFORMATION_SCHEMA', 'ACCOUNT_USAGE', 'ORGANIZATION_USAGE', or 'READER_ACCOUNT_USAGE'.

'object_name'

Name of the object.

Returns¶

Returns a VARCHAR value containing a comma-separated list of the columns specified by the previous call to SYSTEM$SET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT. The column names are in uppercase.

If SYSTEM$SET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT was not called or if SYSTEM$UNSET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT was called to clear the list of columns, the function returns an empty string.

Access control requirements¶

Only account administrators (users who have been granted the ACCOUNTADMIN role) can call this function.

Usage notes¶

• You must have a database in use (for example, by running USE DATABASE) in order to call this function. If no database is in use, the function call fails.

Examples¶

The following example returns the list of columns specified by a previous call to SYSTEM$SET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT for the TABLES view in the ACCOUNT_USAGE schema:

SELECT SYSTEM$GET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT(
  'VIEW',
  'SNOWFLAKE',
  'ACCOUNT_USAGE',
  'TABLES'
);

+--------------------------------------------------------+
| SYSTEM$GET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT( |
|   'VIEW',                                              |
|   'SNOWFLAKE',                                         |
|   'ACCOUNT_USAGE',                                     |
|   'TABLES'                                             |
| )                                                      |
|--------------------------------------------------------|
| TABLE_NAME,TABLE_SCHEMA,TABLE_TYPE                     |
+--------------------------------------------------------+

The following example returns the list of columns specified by a previous call to SYSTEM$SET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT for the TABLES view in the INFORMATION_SCHEMA schema:

SELECT SYSTEM$GET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT(
  'VIEW',
  '',
  'ACCOUNT_USAGE',
  'TABLES'
);

+--------------------------------------------------------+
| SYSTEM$GET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT( |
|   'VIEW',                                              |
|   '',                                                  |
|   'INFORMATION_SCHEMA',                                |
|   'TABLES'                                             |
| )                                                      |
|--------------------------------------------------------|
| TABLE_NAME,TABLE_SCHEMA,TABLE_TYPE                     |
+--------------------------------------------------------+

If SYSTEM$SET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT was not called or if SYSTEM$UNSET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT was called to clear the list, the function returns an empty string:

SELECT SYSTEM$UNSET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT(
  'VIEW',
  'SNOWFLAKE',
  'ACCOUNT_USAGE',
  'TABLES'
);

SELECT SYSTEM$GET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT(
  'VIEW',
  'SNOWFLAKE',
  'ACCOUNT_USAGE',
  'TABLES'
);

+--------------------------------------------------------+
| SYSTEM$GET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT( |
|   'VIEW',                                              |
|   'SNOWFLAKE',                                         |
|   'ACCOUNT_USAGE',                                     |
|   'TABLES'                                             |
| )                                                      |
|--------------------------------------------------------|

The following example returns the list of columns specified by a previous call to SYSTEM$SET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT for the TASK_HISTORY table function in the INFORMATION_SCHEMA schema:

SELECT SYSTEM$GET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT(
  'FUNCTION',
  '',
  'INFORMATION_SCHEMA',
  'TASK_HISTORY'
);