Categories:

System functions (Control)

SYSTEM$SET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT¶

Controls the columns that should be returned when you select all columns (SELECT *) from the specified Snowflake view (for example, from a specific ACCOUNT_USAGE, ORGANIZATION_USAGE, READER_ACCOUNT_USAGE, or INFORMATION_SCHEMA view) or from the output of a built-in table function in one of these schemas.

You can call this function if the introduction of new columns in a Snowflake view or table function introduces a problem with a script or code that selects all columns and depends on a fixed number or order of columns in the results. See Handling new columns in SHOW command output, Snowflake views, and table functions.

See also:

SYSTEM$GET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT , SYSTEM$UNSET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT , SYSTEM$GET_ALL_DEFAULT_COLUMNS_OVERRIDES

Syntax¶

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

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.

list_of_columns

Comma-separated list of columns that should be returned when you select all columns from this view or from the output of this table function.

You can specify the column names in uppercase, lowercase, or mixed case. The columns are returned in their default order, regardless of the order in which you list them.

To return all columns, specify an empty string or call SYSTEM$UNSET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT.

Returns¶

Returns TRUE if the operation was successful.

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 configures queries that select all columns from the TABLES view view in the ACCOUNT_USAGE schema to return only the table_name, table_schema, and table_type columns:

SELECT SYSTEM$SET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT(
  'VIEW',
  'SNOWFLAKE',
  'ACCOUNT_USAGE',
  'TABLES',
  'table_name, table_schema, table_type'
);

Selecting all columns from that view returns only the specified columns:

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES;

+------------+---------------------+------------+
| TABLE_NAME | TABLE_SCHEMA        | TABLE_TYPE |
|------------+---------------------+------------|
| MY_TABLE   | MY_SCHEMA           | BASE TABLE |
+------------+---------------------+------------+

The following example configures queries that select all columns from the TABLES view view in the INFORMATION_SCHEMA schema to return only the table_name, table_schema, and table_type columns:

SELECT SYSTEM$SET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT(
  'VIEW',
  '',
  'INFORMATION_SCHEMA',
  'TABLES',
  'table_name, table_schema, table_type'
);

Selecting all columns from that view returns only the specified columns:

SELECT * FROM INFORMATION_SCHEMA.TABLES;

+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE |
|--------------+------------+------------|
| MY_SCHEMA    | MY_TABLE   | BASE TABLE |
+--------------+------------+------------+

To configure the columns returned by a built-in table function in the INFORMATION_SCHEMA schema, specify 'FUNCTION' as the object type, an empty string as the database name, and 'INFORMATION_SCHEMA' as the schema name. The following example configures the TASK_HISTORY table function to return only the name, state, and scheduled_time columns:

SELECT SYSTEM$SET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT(
  'FUNCTION',
  '',
  'INFORMATION_SCHEMA',
  'TASK_HISTORY',
  'name, state, scheduled_time'
);