Database Dictionary with Extended Property

You can use tools like SQL Doc (Red Gate) to generate database dictionary. Add extended property to table and column when you create them.

Adding extended property to table.

EXEC sp_addextendedproperty N'MS_Description', N'This is the description for the table.', 'SCHEMA', N'dbo', 'TABLE', N'tblTable01', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_Description', N'This is the description for the column.', 'SCHEMA', N'dbo', 'TABLE', N'tblTable01', 'COLUMN', N'col01'
GO

-- Add extended properties for TABLE
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'This is the description for the table.' ,
@level0type=N'SCHEMA', 
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE', 
@level1name=N'tbImageReviewStaging'--Table Name 
GO

-- Add extended properties for COLUMN
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'This is the description for the column.' ,
@level0type=N'SCHEMA', 
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE', 
@level1name=N'tblTable01', --Table Name 
@level2type=N'COLUMN', 
@level2name=N'col01' --Column Name
GO

Update existing extended property to table.

EXEC sp_updateextendedproperty N'MS_Description', N'This table stores data', 'SCHEMA', N'DBO', 'TABLE', N'tblTable01', NULL, NULL
GO

You can also update the extended property of table and column by clicking on the table or column -> Properties -> Extended Properties

Important:

Remember to use “MS_Description” instead of “Description”. SQL documentation software like SQL Doc will uses MS_Description, otherwise it will go to the rest of the extended property list.

Sources:

https://www.mssqltips.com/sqlservertip/5384/working-with-sql-server-extended-properties/