How to Add value to Comment Column in Databricks Catalog View for Foreign table (Azure SQL)

[Leon_K]

Hi.

Struggling to add description by script to the comment column within the catalog view in Databricks, particularly for foreign/external tables sourced from Azure SQL.

I have no issue doing that for delta tables. 

Also for information schema columns for delta tables I see "comment" column, but not for Azure SQL . 

Wondering if it's possible to add "comment" values by script for foreign table in Databricks to appear in the catalog view. 

[Kaniz_Fatma]

Hi @Leon_K, Adding comments to columns within the catalog view in Databricks can be quite useful for documentation and understanding your data. While it’s straightforward for Delta tables, handling foreign/external tables sourced from Azure SQL requires a slightly different approach.

Here are some steps you can follow to add comments to columns for foreign tables in Databricks:

1. Using AI-Generated Comments (Recommended):

   • In your Databricks workspace, navigate to the Catalog section.
   • Search or browse for the specific table (foreign table from Azure SQL) you want to add comments to.
   • On the Columns tab, click the AI generate button. This will generate comments for each column based on AI analysis.
   • You can then click the check mark next to the generated comment to accept it or close it unsaved¹².

2. Using Markdown Comments (Manual Approach):

   • Markdown comments can be added directly to the table description. These comments render in the Catalog Explorer as soon as you save changes.
   • Click the pencil icon to modify comments, and then click Save to update them.
   • You can also use SQL to add table comments during table creation or ALTER TABLE actions³.

3. Programmatic Approach (For Azure SQL):

   • Unfortunately, Azure SQL does not have a direct “comment” column like Delta tables. However, you can set the description programmatically using stored procedures.
   • For example, you can use the following SQL commands to set the description of a column:

     EXEC sp_addextendedproperty @name = N'MS_Description',
                                 @value = 'This is the description of my column',
                                 @level0type = N'Schema',
                                 @level0name = 'dbo';
     

     Replace 'dbo' with the appropriate schema and column name⁴.

4. REST API (Advanced):

   • While Databricks does not provide direct APIs for updating table comments, you can utilize the SQL Statement Execution API to achieve this.
   • Refer to this tutorial for more details on using the SQL Execution API: Tutorial: ALTER TABLE/COLUMN COMMENT⁵.

Remember that documenting your data with meaningful comments enhances collaboration and understanding, so choose the method that best suits your workflow.

Happy cataloging! 📊🗂️

[Leon_K]

@Kaniz_Fatma Sorry, I did read this way back but forgot to reply.
Thanks for information.

And all those steps I found as well and for "3. Programmatic Approach (For Azure SQL)" if you are adding to sql any description that way I don't think it will appear in a databricks workspace - on "comment" field.