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 view or INFORMATION_SCHEMA view).

You can call this function if the introduction of new columns in a Snowflake view 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 and Snowflake views.

See also:

SYSTEM$GET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT , SYSTEM$UNSET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT , SYSTEM$GET_ALL_DEFAULT_COLUMNS_OVERRIDES

语法

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

实参

'object_type'

Type of the object. You must specify 'VIEW' for this argument.

'database_name'

Name of the database that contains the object. You must specify 'SNOWFLAKE' or, for INFORMATION_SCHEMA views, an empty string.

'schema_name'

Name of the schema that contains the object. You must specify the name of a schema in the SNOWFLAKE database or 'INFORMATION_SCHEMA'.

'object_name'

对象名称。

list_of_columns

一个以逗号或空格分隔的列列表，用于指定在从此视图选择所有列时应返回哪些列。

您可以以大写、小写或混合大小写形式指定列名。

要返回所有列，请指定空字符串或调用 SYSTEM$UNSET_DEFAULT_COLUMNS_OVERRIDE_FOR_SYSTEM_OBJECT。

返回

如果操作成功，返回 TRUE。

访问控制要求

只有账户管理员（被授予 ACCOUNTADMIN 角色的用户）可以调用此函数。

使用说明

• 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.

示例

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'
);

从该视图选择所有列时，仅会返回指定的列：

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'
);

从该视图选择所有列时，仅会返回指定的列：

SELECT * FROM INFORMATION_SCHEMA.TABLES;

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