TSQL to read & write SQL extended property: MS_Description

create.sql

CREATE TABLE dbo.STATE (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Description NVARCHAR(255)
);

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Table holding state information', 
    @level0type = N'user', @level0name = dbo,
    @level1type = N'table', @level1name = 'STATE';

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Unique identifier for the state', 
    @level0type = N'user', @level0name = dbo,
    @level1type = N'table', @level1name = 'STATE',
    @level2type = N'column', @level2name = 'ID';

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'The name of the state', 
    @level0type = N'user', @level0name = dbo,
    @level1type = N'table', @level1name = 'STATE',
    @level2type = N'column', @level2name = 'Name';

read.sql

DECLARE @tname VARCHAR(1000) = 'STATE';

SELECT 
    Name = SCHEMA_NAME(t.schema_id) + '.' + t.name,
    Type = 'Table',
    Description = CAST(ep.value AS VARCHAR(8000)),
    DataType = NULL -- Tables don't have data types
FROM sys.tables t
LEFT JOIN sys.extended_properties ep 
    ON t.object_id = ep.major_id AND ep.minor_id = 0 AND ep.name = 'MS_Description'
WHERE t.name = @tname

UNION ALL

SELECT 
    Name = SCHEMA_NAME(t.schema_id) + '.' + t.name + '.' + c.name,
    Type = 'Column',
    Description = CAST(ep.value AS VARCHAR(8000)),
    DataType = t.name + 
        CASE 
            WHEN t.name IN ('varchar', 'nvarchar', 'char', 'nchar', 'varbinary') THEN '(' + CAST(c.max_length AS VARCHAR) + ')'
            WHEN t.name = 'decimal' THEN '(' + CAST(c.precision AS VARCHAR) + ',' + CAST(c.scale AS VARCHAR) + ')'
            ELSE ''
        END
FROM sys.columns c
INNER JOIN sys.tables tb 
    ON c.object_id = tb.object_id
LEFT JOIN sys.types t 
    ON c.user_type_id = t.user_type_id
LEFT JOIN sys.extended_properties ep 
    ON c.object_id = ep.major_id AND c.column_id = ep.minor_id AND ep.name = 'MS_Description'
WHERE tb.name = @tname;