Need to identify the user, not role, that created a table

user_9795777 0
Viewed 70

I am trying to associate an object (table) back to the user that created it. Since objects are owned by the active role, how can I get a list of tables created by a single user? I've searched QUERY_HISTORY for the create table statements, but looking for a better solution.

snowflake-cloud-data-platform
2 Answers

Does something like this help or is this what you are doing now? I think it's the main solution available right now. You can, of course, change the filters as desired including filtering by a specific user or timeframe.

use role accountadmin;
use schema snowflake.account_usage;
select q.start_time created_time, q.user_name, t.table_name, q.query_text, t.table_schema schemas_name, T.table_catalog database_name from query_history q
inner join tables t 
on q.start_time = t.created
and q.schema_name = t.table_schema
and q.database_name = t.table_catalog
and q.query_text ilike 'create%table%'
and q.start_time > '2020-11-04 11:13:54'
order by q.start_time;
user_121657560

You should be able to do it with a query like this

use schema snowflake.account_usage;    
select *
    from QUERY_HISTORY
    where 
    database_name = 'TPCDS'
    and query_type = 'CREATE'
    ;
user_144009980
Related