Statements
2 0
Read Time:1 Minute, 41 Second

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.

Tables and Columns

We have written below stored procedure.

Stored Procedure

Execute the procedure and see the below output:

As we see total 30 statements for all the columns present in Tables.

Proc output

 

 

 

 

 

 

 

 

 

 

Below are the statements generated by proc:

Statements

Validate the Comments on Column in all tables:

Verify Comments

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

Leave a Reply

Your email address will not be published. Required fields are marked *