为字段加入说明，以及查看字段信息

declare @table_name nvarchar(50)
declare @column_name nvarchar(50)
declare @Description nvarchar(50)

--01
set @table_name=N'CCOSBusiness'
set @column_name=N'BusinessId'
set @Description=N'业务流水号'
IF EXISTS (
SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'CCOS', N'TABLE',@table_name, N'COLUMN',@column_name))
begin
EXEC sys.sp_dropextendedproperty @name=N'MS_Description',
                                @level0type=N'SCHEMA',
                                @level0name=N'CCOS',
                                @level1type=N'TABLE',
                                @level1name=@table_name,
                                @level2type=N'COLUMN',
                                @level2name=@column_name
end
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
                                @value=@Description,
                                @level0type=N'SCHEMA',
                                @level0name=N'CCOS',
                                @level1type=N'TABLE',
                                @level1name=CCOSBusiness,
                                @level2type=N'COLUMN',
                                @level2name=@column_name

                               
SELECT [table name] = i_s.table_name,
       [column name] = i_s.column_name,
       [description] = s.VALUE ,
       [DATA_TYPE]=i_s.DATA_TYPE
FROM   information_schema.columns i_s
       LEFT OUTER JOIN sys.extended_properties s
         ON s.major_id = Object_id(i_s.table_schema + '.' + i_s.table_name)
            AND s.minor_id = i_s.ordinal_position
            AND s.name = 'ms_description'
WHERE  Objectproperty(Object_id(i_s.table_schema + '.' + i_s.table_name),
       'ismsshipped'
       ) = 0 
--and i_s.table_name = 'meter' 
ORDER  BY i_s.table_name,
          i_s.ordinal_position