Can SQL Doc descriptions be accessed in Power BI

SJGrdn

Would like to display them on a reference page in reports.

Jon_Kirkwood

Hi  @SJGrdn

 Thank you for reaching out on the Redgate forums regarding your SQL Doc descriptions question.

These values are stored in the database that is being documented, you could very likely read them directly from the database into your Power BI project.

 

As an example of where the values are, I have the following SQL code that pulls descriptions for tables & columns. This will list them all for the database you run it against, but may be further tweaked for your needs. 

I believe in Power BI, you can import a SQL Server instance and provide a SQL statement to run. It's not my area of expertise but have run into a similar scenario before.

 

-- Descriptions for tables
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    ep.value AS TableDescription
FROM 
    sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    LEFT JOIN sys.extended_properties ep ON ep.major_id = t.object_id
        AND ep.minor_id = 0
        AND ep.name = 'MS_Description'
WHERE [ep].[value] IS NOT null
ORDER BY 
    SchemaName, TableName;

-- Descriptions for columns
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    ep.value AS ColumnDescription
FROM 
    sys.columns c
    INNER JOIN sys.tables t ON c.object_id = t.object_id
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    LEFT JOIN sys.extended_properties ep ON ep.major_id = c.object_id
        AND ep.minor_id = c.column_id
        AND ep.name = 'MS_Description'
WHERE [ep].[value] IS NOT null
ORDER BY 
    SchemaName, TableName, ColumnName;

 

Example below - setting an MS_Description value of 'test' and how it can be found when querying the database