获取、增加、修改、删除sqlserver字段描述及快速查看表字段与描述

先看添加与删除字段描述

EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    --添加Type字段说明    
 @value = N'屏蔽类型对应值(类型对应Id)',
     @level0type = N'SCHEMA',
     @level0name = N'dbo',
     @level1type = N'TABLE',
     @level1name = N'ForbiddenType',
     @level2type = N'COLUMN',
     @level2name = N'TypeId' 



  --删除表中列Type的描述属性：                               
EXEC sp_dropextendedproperty 'MS_Description',
     'SCHEMA',
     dbo,
     'TABLE',
     'ForbiddenType',
     'COLUMN',
     TYPE  

看下面的实例：

--创建表及描述信息 

create table 表(a1 varchar(10),a2 char(2)) 

--为表添加描述信息 
EXECUTE sp_addextendedproperty N'MS_Description', '人员信息表', N'user', N'dbo', N'table', N'表', NULL, NULL 

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生成记录表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CrabRequisition'
GO

--为字段a1添加描述信息 
EXECUTE sp_addextendedproperty N'MS_Description', '姓名', N'user', N'dbo', N'table', N'表', N'column', N'a1'

EXECUTE sp_addextendedproperty N'MS_Description', '测试', N'user', N'dbo', N'table', N'HR_Employees', N'column', N'test' 

--为字段a2添加描述信息 
EXECUTE sp_addextendedproperty N'MS_Description', '性别', N'user', N'dbo', N'table', N'表', N'column', N'a2' 
--更新表中列a1的描述属性： 
EXEC sp_updateextendedproperty 'MS_Description','字段1','user',dbo,'table','表','column',a1 

--删除表中列a1的描述属性： 
EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table','表','column',a1 

 

--表描述  
SELECT
    tbs.name 表名
   ,ds.value 描述
FROM sys.extended_properties ds
LEFT JOIN sysobjects tbs
    ON ds.major_id = tbs.id
WHERE ds.minor_id = 0
AND tbs.name = 'Warrant_BaseInfo';--表名

--快速查看表结构  
SELECT
    CASE
        WHEN col.colorder = 1 THEN obj.name
        ELSE ''
    END AS 表名
   ,col.colorder AS 序号
   ,col.name AS 列名
   ,ISNULL(ep.[value], '') AS 列说明
   ,t.name AS 数据类型
   ,col.length AS 长度
   ,ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数
   ,CASE
        WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'
        ELSE ''
    END AS 标识
   ,CASE
        WHEN EXISTS (SELECT
                    1
                FROM dbo.sysindexes si
                INNER JOIN dbo.sysindexkeys sik
                    ON si.id = sik.id
                    AND si.indid = sik.indid
                INNER JOIN dbo.syscolumns sc
                    ON sc.id = sik.id
                    AND sc.colid = sik.colid
                INNER JOIN dbo.sysobjects so
                    ON so.name = si.name
                    AND so.xtype = 'PK'
                WHERE sc.id = col.id
                AND sc.colid = col.colid) THEN '√'
        ELSE ''
    END AS 主键
   ,CASE
        WHEN col.isnullable = 1 THEN '√'
        ELSE ''
    END AS 允许空
   ,ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t
    ON col.xtype = t.xusertype
INNER JOIN dbo.sysobjects obj
    ON col.id = obj.id
        AND obj.xtype = 'U'
        AND obj.status >= 0
LEFT JOIN dbo.syscomments comm
    ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep
    ON col.id = ep.major_id
        AND col.colid = ep.minor_id
        AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties epTwo
    ON obj.id = epTwo.major_id
        AND epTwo.minor_id = 0
        AND epTwo.name = 'MS_Description'
WHERE obj.name = 'Ath_LoanApply_tbl'--表名  
ORDER BY col.colorder;  

 

IF (( SELECT
        COUNT(*)
    FROM fn_listextendedproperty('MS_Description',
    'SCHEMA', N'dbo',
    'TABLE', N'Fct_Order',
    'COLUMN', N'Carrier'))
> 0) EXEC sp_updateextendedproperty    @name = N'MS_Description',
                                    @value = N'承运商',
                                    @level0type = 'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = 'TABLE',
                                    @level1name = N'Fct_Order',
                                    @level2type = 'COLUMN',
                                    @level2name = N'Carrier' ELSE EXEC sp_addextendedproperty    @name = N'MS_Description',
                                                                                                @value = N'承运商',
                                                                                                @level0type = 'SCHEMA',
                                                                                                @level0name = N'dbo',
                                                                                                @level1type = 'TABLE',
                                                                                                @level1name = N'Fct_Order',
                                                                                                @level2type = 'COLUMN',
                                                                                                @level2name = N'Carrier'
GO

 

PrePackage_Header
IF NOT EXISTS ( SELECT  NULL
                FROM    dbo.sysobjects
                WHERE   id = OBJECT_ID(N'[PrePackage_Header]')
                        AND OBJECTPROPERTY(id, 'IsTable') = 1 )
    BEGIN
        CREATE TABLE PrePackage_Header
            (
              PrePackagedId [UNIQUEIDENTIFIER] NOT NULL ,
              WarehouseId [UNIQUEIDENTIFIER] NOT NULL ,
              PrePackagedCodeNumber [VARCHAR](50) NOT NULL ,
              PrePackagedBarCodeNumber [VARCHAR](50) NOT NULL ,
              PrePackagedName [VARCHAR](50) NOT NULL ,
              Disabled INT
                NOT NULL
                CONSTRAINT [DF_PrePackage_Header_Disabled] DEFAULT ( (0) ) ,
              Rec_CreateTime DATETIME
                NULL
                CONSTRAINT [DF_PrePackage_Header_Rec_CreateTime]
                DEFAULT ( GETDATE() ) ,
              Rec_CreateBy [VARCHAR](50) NULL ,
              Rec_ModifyTime [DATETIME] NULL ,
              Rec_ModifyBy [VARCHAR](50) NULL ,
              CONSTRAINT [PK_PrePackage_Header] PRIMARY KEY CLUSTERED
                ( PrePackagedId ASC )
                WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                       ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
            )
        ON  [PRIMARY];
        

        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'预包装管理Id', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Header',
            @level2type = N'COLUMN', @level2name = N'PrePackagedId';

        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'仓库Id', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Header',
            @level2type = N'COLUMN', @level2name = N'WarehouseId';

        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'预包装编码', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Header',
            @level2type = N'COLUMN', @level2name = N'PrePackagedCodeNumber';

        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'预包装条码', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Header',
            @level2type = N'COLUMN', @level2name = N'PrePackagedBarCodeNumber';

        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'预包装名称', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Header',
            @level2type = N'COLUMN', @level2name = N'PrePackagedName';
            
        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'是否禁用', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Header',
            @level2type = N'COLUMN', @level2name = N'Disabled';


        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'创建时间', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Header',
            @level2type = N'COLUMN', @level2name = N'Rec_CreateTime';


        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'创建人', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Header',
            @level2type = N'COLUMN', @level2name = N'Rec_CreateBy';


        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'修改时间', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Header',
            @level2type = N'COLUMN', @level2name = N'Rec_ModifyTime';


        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'修改人', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Header',
            @level2type = N'COLUMN', @level2name = N'Rec_ModifyBy';


        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'预包装管理主表', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Header';

    END;
GO


PrePackage_Detail
IF NOT EXISTS ( SELECT  NULL
                FROM    dbo.sysobjects
                WHERE   id = OBJECT_ID(N'[PrePackage_Detail]')
                        AND OBJECTPROPERTY(id, 'IsTable') = 1 )
    BEGIN
        CREATE TABLE PrePackage_Detail
            (
              PrePackage_DetailId [UNIQUEIDENTIFIER] NOT NULL ,
              PrePackagedId [UNIQUEIDENTIFIER] NOT NULL ,
              GoodsId [UNIQUEIDENTIFIER] NULL ,
              GoodsCode [VARCHAR](50) NULL ,
              Qunatity DECIMAL(18, 2) NOT NULL ,
              Disabled INT
                NOT NULL
                CONSTRAINT [DF_PrePackage_Detail_Disabled] DEFAULT ( (0) ) ,
              Rec_CreateTime DATETIME
                NULL
                CONSTRAINT [DF_PrePackage_Detail_Rec_CreateTime]
                DEFAULT ( GETDATE() ) ,
              Rec_CreateBy [VARCHAR](50) NULL ,
              Rec_ModifyTime [DATETIME] NULL ,
              Rec_ModifyBy [VARCHAR](50) NULL ,
              CONSTRAINT [PK_PrePackage_Detail] PRIMARY KEY CLUSTERED
                ( PrePackage_DetailId ASC )
                WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                       ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
            )
        ON  [PRIMARY];
        

        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'预包装明细Id', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Detail',
            @level2type = N'COLUMN', @level2name = N'PrePackage_DetailId';

        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'货品编码', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Detail',
            @level2type = N'COLUMN', @level2name = N'GoodsCode';

        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'货品Id', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Detail',
            @level2type = N'COLUMN', @level2name = N'GoodsId';

        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'数量', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Detail',
            @level2type = N'COLUMN', @level2name = N'Qunatity';
            
        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'是否删除', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Detail',
            @level2type = N'COLUMN', @level2name = N'Disabled';


        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'创建时间', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Detail',
            @level2type = N'COLUMN', @level2name = N'Rec_CreateTime';


        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'创建人', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Detail',
            @level2type = N'COLUMN', @level2name = N'Rec_CreateBy';


        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'修改时间', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Detail',
            @level2type = N'COLUMN', @level2name = N'Rec_ModifyTime';


        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'修改人', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Detail',
            @level2type = N'COLUMN', @level2name = N'Rec_ModifyBy';


        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'预包装明细', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Detail';

    END;
GO