TE-UpdateDescriptionsFromExtProps.cs

using System.Data.SqlClient;
using sysData = System.Data;

// Update the following 2 variables for your environment
var sqlConnStr = "Data Source=localhost\\sql19;Database=AdventureWorksDW2019;Integrated Security=SSPI";
var isPowerBI = true;

// Get all "PBI_Description" column extended Properties
SqlConnection conn = new SqlConnection(sqlConnStr);
conn.Open();
var queryCols = @"
WITH cols as
(SELECT
    tbl.object_id as TableID,
    col.column_id as ColumnID,
   SCHEMA_NAME(tbl.schema_id) AS SchemaName,    
   tbl.name AS TableName, 
   COALESCE( prop.value, col.name) AS ColumnName
FROM
   sys.tables AS tbl
   INNER JOIN sys.all_columns AS col 
     ON col.object_id=tbl.object_id
   LEFT JOIN sys.extended_properties AS prop 
     ON prop.major_id=tbl.object_id 
     AND prop.minor_id=col.column_id 
     AND prop.class=1
     AND prop.name = 'PBI_Column'
     )
, tabs as (
SELECT
    tbl.schema_id as SchemaID,
    tbl.object_id as TableID,
   SCHEMA_NAME(tbl.schema_id) AS SchemaName,    
   COALESCE( prop.value, tbl.name) AS TableName
FROM
   sys.tables AS tbl
   LEFT JOIN sys.extended_properties AS prop 
     ON prop.major_id=tbl.object_id 
     AND prop.class=1
     AND prop.name = 'PBI_Table'
)
SELECT
   SCHEMA_NAME(tabs.SchemaID) AS SchemaName,    
   tabs.TableName, 
   cols.ColumnName,
   prop.name AS ExtendedPropertyName,
   CAST(prop.value AS sql_variant) AS ExtendedPropertyValue
FROM
   tabs
   INNER JOIN cols
     ON cols.TableID=tabs.TableID
   INNER JOIN sys.extended_properties AS prop 
     ON prop.major_id=tabs.TableID
     AND prop.minor_id=cols.ColumnID
     AND prop.class=1
     AND prop.name = 'PBI_Description'
";
var queryTabs = @"

WITH tabs as (
SELECT
    tbl.schema_id as SchemaID,
    tbl.object_id as TableID,
   SCHEMA_NAME(tbl.schema_id) AS SchemaName,    
   COALESCE( prop.value, tbl.name) AS TableName
FROM
   sys.tables AS tbl
   LEFT JOIN sys.extended_properties AS prop 
     ON prop.major_id=tbl.object_id 
     AND prop.class=1
     AND prop.name = 'PBI_Table'
)
SELECT
   SCHEMA_NAME(tabs.SchemaID) AS SchemaName,    
   tabs.TableName, 
   prop.name AS ExtendedPropertyName,
   CAST(prop.value AS sql_variant) AS ExtendedPropertyValue
FROM
   tabs
   INNER JOIN sys.extended_properties AS prop 
     ON prop.major_id=tabs.TableID
     and prop.minor_id = 0
     AND prop.class=1
     AND prop.name = 'PBI_Description'
";
SqlDataAdapter da = new SqlDataAdapter(queryCols, conn);
var tableCols = new sysData.DataTable();
da.Fill(tableCols);

// Update column descriptions
foreach (sysData.DataRow row in tableCols.Rows)
{
    var tbl = Model.Tables.First((t) => t.Name == row["TableName"].ToString());
    var col = tbl.Columns.OfType<DataColumn>().First((c) => c.SourceColumn == row["ColumnName"].ToString()); 
    col.Description = row["ExtendedPropertyValue"].ToString();
}

// The following loop will only run if you change the isPowerBI variable to false at the 
// top of the script. Do NOT do this if you are running against a model in Power BI Desktop
if (!isPowerBI)
{
SqlDataAdapter da2 = new SqlDataAdapter(queryTabs, conn);
var tableTabs = new sysData.DataTable();
da2.Fill(tableTabs);

// update Table descriptions
foreach (sysData.DataRow row in tableTabs.Rows)
{
    var tbl = Model.Tables.First((t) => t.Name == row["TableName"].ToString());
    tbl.Description = row["ExtendedPropertyValue"].ToString();
}
}