How to find the user who created a table in Snowflake

Question

I am trying to find the created by user for a table in Snowflake. Information_Schema tables only shows the role that owns it.

Going through the Query_History is laborious.

Thanks, Sam

Information_Schema and Show tables command.

Answer 1

random searches in snowflake.account_usage.query_history can be laborious, but targeted ones are fairly quick and easy, this should work for you.

SET dbName='DATABASE NAME HERE';
SET schemaName='SCHEMA NAME HERE';
SET tableName='TABLE NAME HERE';
SET create_dt=(
    SELECT MIN(created) 
    FROM snowflake.account_usage.tables 
    WHERE table_catalog = $dbName 
    AND table_schema = $schemaName 
    AND table_name = $tableName 
    AND deleted is null);

SELECT  * 
FROM   snowflake.account_usage.query_history
WHERE  query_text iLike '%CREATE%TABLE%'||$tableName||'%'
AND    execution_status = 'SUCCESS'
AND    start_time = $create_dt;

Answer 2

Posting the query I came up with to find user/role of the person who created a table for anyone that wants to adapt it.

// anything older than 20 days ago
SET cutoff = current_date() - 20;

SELECT h.query_text,
       h.database_name,
       h.user_name,
       h.role_name,
       t.table_name,
       u.login_name
  FROM snowflake.account_usage.query_history h 
  LEFT JOIN snowflake.account_usage.users u
    ON u.name = h.user_name 
   AND u.disabled = FALSE 
   AND u.deleted_on IS NULL
  JOIN user_stage.information_schema.tables t
    ON t.created = h.start_time
 WHERE execution_status = 'SUCCESS' 
   AND start_time < $cutoff
   AND query_type LIKE '%CREATE_TABLE%'
   AND CONTAINS(UPPER(h.query_text), UPPER(t.table_name))
   AND t.created < $cutoff;