What are the challenges you face when working across database platforms? Take the survey
Options

Can SQL Doc descriptions be accessed in Power BI

SJGrdnSJGrdn Posts: 1 New member
Would like to display them on a reference page in reports.
Tagged:

Answers

  • Options

    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 

     

    Jon Kirkwood | Technical Support Engineer | Redgate Software
Sign In or Register to comment.