SQL Server Add, update, query table comment, field comment-related SQL

/******************* Field Add Comment *********************/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 ' table name ' and b.name=n ' field name ') EXEC sys.sp_addextendedproperty @name =n ' ms_description ' , @value =n ' field Notes ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' name ', @level2type =n ' COLUMN ', @level2name =n ' field name ' EXEC sp_updateextendedproperty @name =n ' ms_description ', @value =n ' field comment ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' name ', @level2type =n ' COLUMN ', @level2name =n ' field name '/*** Table Add Comment *********************/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 ' table name ') EXEC Sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' table Notes ', @level0type =n ' SCHEMA ', @level0naMe=n ' dbo ', @level1type =n ' table ', @level1name =n ' name ' EXEC sp_updateextendedproperty @name =n ' ms_description ', @value =n ' Table Notes ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' name '/******************* Query comment Related sql***********************/--view table comments 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 ' table name '--view field comments 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_p Roperties C on c.major_id = b.object_id and c.minor_id = b.column_idwhere a.name = N ' Luobo '

　　

SQL Server Add, update, query table comment, field comment-related SQL