Sql Server 添加、更新、查詢表注釋、欄位注釋相關sql

標籤：

/*******************欄位添加註釋*********************/ if not exists (SELECTC.value AS column_descriptionFROM sys.tables AINNER JOIN sys.columns B ON B.object_id = A.object_idINNER JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_idWHERE A.name = N‘表名‘ and B.name=N‘欄位名‘)   EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘欄位注釋‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘表名‘, @level2type=N‘COLUMN‘,@level2name=N‘欄位名‘EXEC sp_updateextendedproperty @name=N‘MS_Description‘, @value=N‘欄位注釋‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘表名‘, @level2type=N‘COLUMN‘,@level2name=N‘欄位名‘/*******************表添加註釋*********************/ if not exists (SELECTA.name,C.valueFROM sys.tables Ainner JOIN sys.extended_properties C ON C.major_id = A.object_id  and minor_id=0WHERE A.name = N‘表名‘)EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘表注釋‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘表名‘EXEC sp_updateextendedproperty @name=N‘MS_Description‘, @value=N‘表注釋‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘表名‘/*******************查詢注釋相關sql***********************/--查看錶的注釋SELECTA.name,C.valueFROM sys.tables Ainner JOIN sys.extended_properties C ON C.major_id = A.object_id  and minor_id=0WHERE A.name = N‘表名‘--查看欄位的注釋SELECTA.name AS table_name,B.name AS column_name,C.value AS column_descriptionFROM sys.tables AINNER JOIN sys.columns B ON B.object_id = A.object_idLEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_idWHERE A.name = N‘luobo‘

　　

Sql Server 添加、更新、查詢表注釋、欄位注釋相關sql