Can SQL Doc descriptions be accessed in Power BI

• 

  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 May 21, 2024 06:40

  - 0 +

Add comment

Please sign in to leave a comment.