CODEWITHSUNDEEP
snowflake-cloud-data-platform
Sri
Sri

Reputation: 75

Snowflake SQL Procedure to Alter all tables in a database or schema

I'm trying to write a snowflake sql stored procedure to alter all tables in a database. I know we can get a table names from querying information schema eg: select table_name from DEMO_DB.INFORMATION_SCHEMA.TABLES;

I can do it for one table like below

create or replace procedure enable_change_tracking(TABLE_NAME varchar)
  returns varchar
  language javascript
  as     
  $$  
    var my_sql_command = "ALTER table "+ TABLE_NAME +" SET CHANGE_TRACKING = TRUE;"
    
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
 
       
  return 'Done.'; 
  $$
  ;'''

call enable_change_tracking('table_name');

How do i pass the result set of '''select table_name from DEMO_DB.INFORMATION_SCHEMA.TABLES;''' to a above stored procedure?

Upvotes: 2

Views: 1624

Answers (3)

Sri
Sri

Reputation: 75

Answer

create or replace procedure enable_change_tracking_st()
returns varchar
language javascript
as     
$$  
    var my_sql_command = "select table_name from DEMO_DB.INFORMATION_SCHEMA.TABLES where table_schema = 'PUBLIC'"
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var r = statement1.execute();

    while(r.next()) {
        var table_name = r.getColumnValue(1);
        var sub_q = "ALTER table "+ table_name +" SET CHANGE_TRACKING = TRUE;"
        var statement2 = snowflake.createStatement( {sqlText: sub_q} );
        var r2 = statement2.execute();
    }
return 'Done';
$$
;

Upvotes: 2

Jeffrey Jacobs
Jeffrey Jacobs

Reputation: 332

I generally write something to produce a script. Usually a simple UDF which takes the table name and the desired DDL statement. That lets me test it without running it against everything. Once I've tested it, I then do something along the lines of: SELECT UDF_GEN_MY_QUERT(TABLE_NAME) FROM (SELECT TABLE_NAME FROM ...).

As an example, I have a meta-data table to created Snowflake object from Salesforce Object meta-data.

On Friday, I deployed 168 new/updated tables, VIEWs, STREAM and TASKs using this technique, into 4 databases (3 dev DBs, 1 INT DB.)

I will be publishing an article on Linkedin, Snowflake medium blog and my own wordpress blog shortly.

Upvotes: 1

Eric Lin
Eric Lin

Reputation: 1510

As Greg mentioned, you need to loop through the result in the SP:

create or replace procedure enable_change_tracking()
returns varchar
language javascript
as     
$$  
    var my_sql_command = "select table_name from DEMO_DB.INFORMATION_SCHEMA.TABLES"
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var r = statement1.execute();

    while(r.next()) {
        table_name = r.getColumnValue('TABLE_NAME');
        sub_q = "ALTER table "+ table_name +" SET CHANGE_TRACKING = TRUE;"
        
        // run your query here
    }
return a;
$$
;

Upvotes: 2

Related Questions