create schema but for a list of schemas

Question

What's the best programmatic option to create multiple schemas for a list of user names?

I wanted to do it for convenience, so when user logins into snowflake they'll have their environment ready. Users get synced by scim proto, so I can get a list from a group they are assigned.

Thanks!

Answer 1

The script could be generated for instance by using: ACCOUNT_USAGE.USERS:

USE ROLE ACCOUNTADMIN;


WITH schema_cte AS (
  SELECT 
    REPLACE('CREATE SCHEMA IF NOT EXISTS <name> /*CLONE <source schema>*/;'||CHAR(13)
           , '<name>', DISPLAY_NAME) AS create_schema_line
  FROM SNOWFLAKE.ACCOUNT_USAGE.USERS
  -- WHERE additional_condition_here
)
SELECT 
    'USE ROLE <role_name_here>;' || CHAR(13) ||
    'USE DATABASE <database_name_here>;' || CHAR(13) ||
    LISTAGG(create_schema_line) WITHIN GROUP(ORDER BY create_schema_line) AS script
FROM schema_cte;

Related: Identifier Requirements