MSSQL / column comment / add , update, delete

- 조회

SELECT 
    obj.name AS TableName, 
    col.name AS ColumnName, 
    ep.value AS ColumnComment
FROM 
    sys.extended_properties AS ep
INNER JOIN 
    sys.objects AS obj ON ep.major_id = obj.object_id
INNER JOIN 
    sys.columns AS col ON ep.major_id = col.object_id AND ep.minor_id = col.column_id
WHERE 
    obj.type_desc = 'USER_TABLE' 
    AND ep.class_desc = 'OBJECT_OR_COLUMN' 
    AND ep.name = 'MS_Description';

 

- 주석 추가

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = 'Your comment here',
    @level0type = N'SCHEMA', 
    @level0name = 'dbo', 
    @level1type = N'TABLE', 
    @level1name = 'YourTableName', 
    @level2type = N'COLUMN', 
    @level2name = 'YourColumnName';

 

-주석 수정

EXEC sp_updateextendedproperty 
    @name = N'MS_Description', 
    @value = 'Your new comment here',
    @level0type = N'SCHEMA', 
    @level0name = 'dbo', 
    @level1type = N'TABLE', 
    @level1name = 'YourTableName', 
    @level2type = N'COLUMN', 
    @level2name = 'YourColumnName';

 

-주석 삭제

EXEC sp_dropextendedproperty 
    @name = N'MS_Description', 
    @level0type = N'SCHEMA', 
    @level0name = 'dbo', 
    @level1type = N'TABLE', 
    @level1name = 'YourTableName', 
    @level2type = N'COLUMN', 
    @level2name = 'YourColumnName';