如何给sqlserver 的表添加注释

字段注释貌似可以在设计表时就可以加或用脚本

那表如何加注释呢
比如

表名：user
注释：用户表

EXECUTE sp_addextendedproperty N’MS_Description’, N’教师开课表’, N’user’, N’dbo’, N’table’, N’TeacherCourse’, NULL, NULL

SQL code

CREATE TABLE [dbo].[T_CollegeInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CollegeName] [nvarchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
    [HeadPhotoUrl] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
    [OrderBy] [int] NULL,
    [IsIndexShow] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
    [IndexOrderBy] [int] NULL,
    [UseFlag] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
    [AddTime] [datetime] NULL,
    [AddUserId] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK__T_CollegeInfo__4222D4EF] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学校名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_CollegeInfo', @level2type=N'COLUMN',@level2name=N'CollegeName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_CollegeInfo', @level2type=N'COLUMN',@level2name=N'OrderBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否显示在主页' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_CollegeInfo', @level2type=N'COLUMN',@level2name=N'IsIndexShow'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主页显示排序' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_CollegeInfo', @level2type=N'COLUMN',@level2name=N'IndexOrderBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否使用' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_CollegeInfo', @level2type=N'COLUMN',@level2name=N'UseFlag'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'增加时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_CollegeInfo', @level2type=N'COLUMN',@level2name=N'AddTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'增加人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_CollegeInfo', @level2type=N'COLUMN',@level2name=N'AddUserId'sp_addextendedproperty 用法参考联机文档。

查看并导出SQLServer2000数据表中字段的注释信息的语法（完整）

导出某个表注释的语句：（示例的表名为：bbs_bank_log）

SELECT sysobjects.name AS 表名, syscolumns.name AS 列名,
      systypes.name AS 数据类型, syscolumns.length AS 数据长度, CONVERT(char,
      sysproperties.[value]) AS 注释
FROM sysproperties RIGHT OUTER JOIN
      sysobjects INNER JOIN
      syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
      systypes ON syscolumns.xtype = systypes.xtype ON
      sysproperties.id = syscolumns.id AND
      sysproperties.smallid = syscolumns.colid
WHERE (sysobjects.xtype = 'u' OR
       sysobjects.xtype = 'v') AND (systypes.name <> 'sysname')
--    and CONVERT(char,sysproperties.[value]) <> 'null' --导出注释不为'null'的记录
--     AND (sysobjects.name = 'bbs_bank_log')     --逐个关联表名，可以用or连接条件
ORDER BY 表名

特别说明：

--以上语句是导出某个表的所有注释，如果要简单的列出表的所有注释，那么语句如下：
SELECT CONVERT(char, [value]) AS Expr1
FROM sysproperties

-- sysobjects为系统对象表，syscolumns为系统字段信息表，systypes为系统类型表。
-- 通过字段的ID和sysproperties（系统注释属性表）关联，就可以读出注释信息。

示例演示：

查看sqlserver注释

SELECT
    A.name AS table_name,
    B.name AS column_name,
    C.value AS column_description
FROM sys.tables A
INNER JOIN sys.columns B
    ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C
    ON C.major_id = B.object_id
        AND C.minor_id = B.column_id
WHERE A.name = 'cdinfo'

--sqlserver用语句给表注释
EXECUTE sp_addextendedproperty N'MS_Description', N'CD信息表', N'user', N'dbo', N'table', N'CDinfo', NULL, NULL

--sqlserver用语句给表的“字段”注释
EXECUTE sp_addextendedproperty N'MS_Description', N'CD唱片名称', N'user', N'dbo', N'table', N'CDinfo', N'COLUMN', N'CdName'

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/lingtw/archive/2009/08/19/4462057.aspx