How do I prevent people from running SELECT * on Snowflake tables?-CodePudding

For compliance reasons we want to block SQL analysts from running SELECT * on a table. Instead, we want to force them to explicitly ask for the columns they want to select. How can I enforce this with Snowflake?

I saw a tip for SQL server using a calculated column, does Snowflake have an equivalent?

You can also append a computed column to an existing table for the same effects:
```
alter table mytable2
add column no_select_star number as (1/0);
```
In action:

CodePudding user response：

it's a workaround and by definition workarounds are not ideal solutions. What I'm wondering is if there's some way to get a message to the user that it's the select * that's causing the error. I tried a JS UDF that throws an error, but that can't be used as a default for a column.

It is possible to use truncation error to display custom message:
```
create or replace table mytable (
    i number, s string
    , no_select_star string as ('Code smell: SELECT * '::CHAR(1))
);
    
INSERT INTO mytable(i, s)  VALUES (1, 'a');
```
Query:
```
SELECT * FROM  mytable;
```
Output: