2
0
In this blog post, we delve into a unique scenario involving Snowflake SQL Scripting. Recently, a colleague presented an uncommon challenge—adding comments to columns. While comments can be added to various objects like users, roles, warehouses, databases, tables, etc., or can be add to individual table columns as well. Often, comments are left null during the creation of columns or other objects.
The specific requirement emerged to add comments to every column within a table, a task that extends across all tables within a particular schema. Although the syntax to update comments on a column is straightforward, such as:
COMMENT ON COLUMN DB.SCHEMA.TABLE.COLUMN is 'Comment';
This approach becomes impractical when dealing with an extensive list of tables, each potentially containing a large number of columns. Manually crafting statements for every table and column in such a scenario is not a viable solution.
To address this challenge, I’ve developed a stored procedure using SQL scripting. While JavaScript is another option, I opted for SQL scripting to enhance my learning in this domain. In this blog, I will walk you through the implementation of this solution, demonstrating how SQL scripting can be a powerful tool in efficiently handling such complex tasks.
In below screenshot we have CMS database having 6 tables and total count of columns is 30. As we can see the COMMENT on these columns is null.
We have written below stored procedure.
Execute the procedure and see the below output:
As we see total 30 statements for all the columns present in Tables.
Below are the statements generated by proc:
Validate the Comments on Column in all tables:
