SQL Server 注释管理

1. 添加表注释

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
                              , @value = N'$Annotation$'
                              , @level0type = N'SCHEMA'
                              , @level0name = N'$dbo$'
                              , @level1type = N'TABLE'
                              , @level1name = N'$TblName$';
GO

2. 添加列注释

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
                              , @value = N'$Annotation$'
                              , @level0type = N'SCHEMA'
                              , @level0name = N'$dbo$'
                              , @level1type = N'TABLE'
                              , @level1name = N'$TblName$'
                              , @level2type = N'COLUMN'
                              , @level2name = N'$ColName$';

3. 查看表结构及注释

USE $DBName$;
GO

DECLARE @objId INT = OBJECT_ID('$DBName$.$dbo$.$TblName$');

SELECT    0 ordinal, CONCAT('[', sch.name, '].[', tbl.name, ']') colName, N'' colType, '' allowNull, N'' defaultValue, CAST(extProp.value AS VARCHAR(MAX)) colDesc
FROM      sys.tables tbl
LEFT JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id
LEFT JOIN sys.extended_properties extProp ON extProp.major_id = tbl.object_id AND extProp.minor_id = 0 AND extProp.name = 'MS_Description'
WHERE     tbl.object_id = @objId
UNION ALL
SELECT     col.column_id
         , col.name
         , UPPER(tp.name)
           + CASE WHEN tp.name IN ('binary', 'char', 'varbinary', 'varchar') THEN '(' + FORMAT(col.max_length, '0') + ')'
                  WHEN tp.name IN ('nchar', 'nvarchar') THEN '(' + FORMAT(col.max_length / 2, '0') + ')'
                  WHEN tp.name IN ('datetime2', 'datetimeoffset', 'time') THEN '(' + FORMAT(COLUMNPROPERTY(tbl.object_id, col.name, 'Scale'), '0') + ')'
                  WHEN tp.name IN ('decimal', 'numeric') THEN
                    '(' + FORMAT(COLUMNPROPERTY(tbl.object_id, col.name, 'Precision'), '0') + ', ' + FORMAT(COLUMNPROPERTY(tbl.object_id, col.name, 'Scale'), '0')
                    + ')'
                  ELSE ''
             END
         , CASE WHEN col.is_nullable = 1 THEN '√' ELSE '' END
         , ISNULL(OBJECT_DEFINITION(col.default_object_id), '')
         , CONCAT_WS(
             ', '
           , CASE WHEN EXISTS (
                         SELECT 1
                         FROM   sys.index_columns pkIdxCol
                         WHERE  pkIdxCol.object_id = tbl.object_id
                                AND pkIdxCol.column_id = col.column_id
                                AND pkIdxCol.index_id IN (
                                      SELECT idx.index_id
                                      FROM   sys.indexes idx
                                      WHERE  idx.object_id = tbl.object_id
                                             AND idx.name IN (SELECT pk.name FROM sys.objects pk WHERE pk.parent_object_id = tbl.object_id AND pk.type = 'PK')
                                    )
                       ) THEN '#主键'
                  WHEN col.is_identity = 1 THEN '#标识'
             END
           , CAST(extProp.value AS VARCHAR(MAX))
           )
FROM       sys.tables tbl
INNER JOIN sys.columns col ON col.object_id = tbl.object_id
LEFT JOIN  sys.types tp ON tp.user_type_id = col.user_type_id
LEFT JOIN  sys.extended_properties extProp ON extProp.major_id = tbl.object_id AND extProp.minor_id = col.column_id AND extProp.name = 'MS_Description'
WHERE      tbl.object_id = @objId AND tbl.type = 'U'
ORDER BY ordinal;