Reveng: Generate Table and Column descriptions from database (extend DatabaseModel)

[ErikEJ]

Related to #15037

[ajcvickers]

@ErikEJ Is this something you are interested in sending a PR for?

[ErikEJ]

Yes!

[ErikEJ]

I propose to extend

Microsoft.EntityFrameworkCore.Scaffolding.Metadata.DatabaseTable 

and

  Microsoft.EntityFrameworkCore.Scaffolding.Metadata.DatabaseColumn

with a Comment property. How does that sound?

[ajcvickers]

Sounds good.

[ErikEJ]

SELECT 
SCH.name + '.' + TBL.name AS CommentKey
,SEP.value AS Comment
FROM sys.tables TBL
 INNER JOIN sys.schemas SCH
 ON TBL.schema_id = SCH.schema_id 
 INNER JOIN sys.extended_properties SEP
 ON TBL.object_id = SEP.major_id 
WHERE SEP.class = 1 
 AND SEP.minor_id = 0
 AND (SEP.value <> '1' AND SEP.value <> 1)
 AND SEP.name = 'MS_Description'

UNION

SELECT 
SCH.name + '.' + TBL.name AS CommentKey
,SEP.value AS Comment
FROM sys.views TBL
 INNER JOIN sys.schemas SCH
 ON TBL.schema_id = SCH.schema_id 
 INNER JOIN sys.extended_properties SEP
 ON TBL.object_id = SEP.major_id 
WHERE SEP.class = 1 
 AND SEP.minor_id = 0
 AND (SEP.value <> '1' AND SEP.value <> 1)
 AND SEP.name = 'MS_Description'

UNION

SELECT 
SCH.name + '.' + TBL.name + '.' + COL.name AS CommentKey
,SEP.value AS Comment
FROM sys.extended_properties SEP
 INNER JOIN sys.columns COL
 ON SEP.major_id = COL.object_id 
 AND SEP.minor_id = COL.column_id 
 INNER JOIN sys.tables TBL
 ON SEP.major_id = TBL.object_id 
 INNER JOIN sys.schemas SCH
 ON TBL.schema_id = SCH.schema_id
WHERE SEP.class = 1
 AND (SEP.value <> '1' AND SEP.value <> 1)

UNION

SELECT 
SCH.name + '.' + TBL.name + '.' + COL.name AS CommentKey
,SEP.value AS Comment
FROM sys.extended_properties SEP
 INNER JOIN sys.columns COL
 ON SEP.major_id = COL.object_id 
 AND SEP.minor_id = COL.column_id 
 INNER JOIN sys.views TBL
 ON SEP.major_id = TBL.object_id 
 INNER JOIN sys.schemas SCH
 ON TBL.schema_id = SCH.schema_id
WHERE SEP.class = 1
 AND (SEP.value <> '1' AND SEP.value <> 1)

[ErikEJ]

• Extend databasemodel
• Get Comment from SQL Server MS_Description
• Scaffold HasComment()