How can i traverse all the tables with specific name (let's say'%SP%') and then update a particular column value from each table in snowflake procedure? Also I have to display how many rows updated from each table Like- table1 - xyz Rows updated

CodePudding user response：

You can use

1. SHOW TABLES LIKE '%SP%': https://docs.snowflake.com/en/sql-reference/sql/show-tables.html
2. The information schema or account usage tables-view: https://docs.snowflake.com/en/sql-reference/info-schema/tables.html

But please note: Depending on your privileges and context the results can vary. SHOW has an additional parameter "in account" and INFORMATION_SCHEMA is individual per database. ACCOUNT_USAGE.TABLES is across all databases.

CodePudding user response：

Unless you're going to do this repetatively, a stored procedure is more work than just using a SQL generator. You can use the show command as Marcel notes, or you can use the SNOWFLAKE database to get a list of all matching columns. I used the SNOWFLAKE database in this example, which assumes that nobody's created a new matching column in the last several minutes (it takes some time to reflect new changes in the SNOWFLAKE database).

This query shows how to find matching columns:

-- Find columns in any database matching the pattern '%SP%'. Note: The SNOWFLAKE database has information that may be up to 3 hours later than the changes.
select C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME 
from "SNOWFLAKE"."ACCOUNT_USAGE"."COLUMNS" C
    left join "SNOWFLAKE"."ACCOUNT_USAGE"."TABLES" T on C.TABLE_ID = T.TABLE_ID
where T.TABLE_TYPE = 'BASE TABLE' and C.TABLE_CATALOG not in ('SNOWFLAKE') and C.TABLE_SCHEMA not in ('INFORMATION_SCHEMA') and C.DATA_TYPE = 'TEXT' and  C.COLUMN_NAME like '%SP%';

This query shows a SQL generator that will generate the UPDATE statements.

select listagg(concat('update "', C.TABLE_CATALOG, '"."', C.TABLE_SCHEMA, '"."', C.TABLE_NAME, '" set "', COLUMN_NAME, '"=''NEW_VALUE'' where ', COLUMN_NAME, '=''OLD_VALUE'''), ';\n') || ';'
from "SNOWFLAKE"."ACCOUNT_USAGE"."COLUMNS" C
    left join "SNOWFLAKE"."ACCOUNT_USAGE"."TABLES" T on C.TABLE_ID = T.TABLE_ID
where T.TABLE_TYPE = 'BASE TABLE' and C.TABLE_CATALOG not in ('SNOWFLAKE') and C.TABLE_SCHEMA not in ('INFORMATION_SCHEMA') and C.DATA_TYPE = 'TEXT' and  C.COLUMN_NAME like '%SP%'; 

As far as capturing the row counts updated, that will be in the query history. If this is a one-and-done thing, it's easier to do it this way.