澳门在线威尼斯官方 > 电脑数据库 > SqlServer创建数据表描述及列描述信息

原标题:SqlServer创建数据表描述及列描述信息

浏览次数:151 时间:2019-10-22

示例

 1 -- 查询
 2 SELECT  [TypeId] ,  [TypeName]
 3 FROM    [dbo].[tabBlockType];
 4 
 5 -- USER (不推荐)
 6 -- 添加表 tabBlockType 描述
 7 EXECUTE sp_addextendedproperty N'MS_Description', N'黑名单类型表', N'USER', N'dbo',
 8     N'table', N'tabBlockType';
 9 
10 -- 添加列 TypeId 描述
11 EXECUTE sp_addextendedproperty N'MS_Description', N'黑名单类型id', N'USER', N'dbo',
12     N'TABLE', N'tabBlockType', N'COLUMN', N'TypeId';
13 -- 添加 列 TypeName 描述
14 EXECUTE sp_addextendedproperty N'MS_Description', N'黑名单类型名称', N'USER', N'dbo',
15     N'TABLE', N'tabBlockType', N'COLUMN', N'TypeName';
16 
17 -- 更新列 TypeName 描述
18 EXECUTE sp_updateextendedproperty N'MS_Description', N'黑名单类型', N'USER', N'dbo',
19     N'TABLE', N'tabBlockType', N'COLUMN', N'TypeName';
20 
21 -- SCHEMA (推荐)
22 -- 添加表 tabBlockType 描述
23 EXECUTE sp_addextendedproperty N'MS_Description', N'黑名单类型表', N'SCHEMA', N'dbo',
24     N'table', N'tabBlockType';
25 
26 -- 添加列 TypeId 描述
27 EXECUTE sp_addextendedproperty N'MS_Description', N'黑名单类型id', N'SCHEMA', N'dbo',
28     N'TABLE', N'tabBlockType', N'COLUMN', N'TypeId';
29 -- 添加 列 TypeName 描述
30 EXECUTE sp_addextendedproperty N'MS_Description', N'黑名单类型名称', N'SCHEMA', N'dbo',
31     N'TABLE', N'tabBlockType', N'COLUMN', N'TypeName';
32 
33 -- 更新列 TypeName 描述
34 EXECUTE sp_updateextendedproperty N'MS_Description', N'黑名单类型', N'SCHEMA', N'dbo',
35     N'TABLE', N'tabBlockType', N'COLUMN', N'TypeName';
36 
37 -- 创建 Create sql 脚本 生成的添加描述 脚本
38 
39 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'黑名单类型id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tabBlockType', @level2type=N'COLUMN',@level2name=N'TypeId'
40 GO
41 
42 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'黑名单类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tabBlockType', @level2type=N'COLUMN',@level2name=N'TypeName'
43 GO
44 
45 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'黑名单类型表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tabBlockType'
46 GO

 

sql 总览

威尼斯澳门在线 1

 

始建create 语句时 描述新闻也会导出

威尼斯澳门在线 2

 

More

仿效资料

  • (注:这篇博客中有几点错误的地点,推荐看上边两篇,官方文书档案最准)

参数表明

[ @name ] = { 'property_name' }
Is the name of the property to be added. property_name is sysname and cannot be NULL. Names can also include blank or non-alphanumeric character strings, and binary values.

[ @value= ] { 'value'}
Is the value to be associated with the property. value is sql_variant, with a default of NULL. The size of value cannot be more than 7,500 bytes.

[ @level0type= ] { 'level0_object_type' }
Is the type of level 0 object. level0_object_type is varchar(128), with a default of NULL.
Valid inputs are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, PLAN GUIDE, and NULL.

[ @level0name= ] { 'level0_object_name' }
Is the name of the level 0 object type specified. level0_object_name is sysname with a default of NULL.

[ @level1type= ] { 'level1_object_type' }
Is the type of level 1 object. level1_object_type is varchar(128), with a default of NULL. Valid inputs are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL.

[ @level1name= ] { 'level1_object_name' }
Is the name of the level 1 object type specified. level1_object_name is sysname, with a default of NULL.

[ @level2type= ] { 'level2_object_type' }
Is the type of level 2 object. level2_object_type is varchar(128), with a default of NULL. Valid inputs are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.

[ @level2name= ] { 'level2_object_name' }
Is the name of the level 2 object type specified. level2_object_name is sysname, with a default of NULL.

[ @name = ] { 'property_name' }

要充足的习性名称。property_威尼斯澳门在线 ,name 的数据类型为 sysname,它不可能是 NULL。名称或许还包罗空白或非字母数字字符串和二进制值。

[ @value = ] { 'value' }

就要与质量相关联的值。value 的数据类型为 sql_variant,带有暗中认可设置 NULL。value 的大大小小不能够超越 7,500 字节;不然 SQL Server 会爆发错误。

[ @level0type = ] { 'level0_object_type' }

顾客或顾客定义类型。level0_object_type 的数据类型为 varchar(128),其私下认可值为 NULL。有效的输入是 ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SEPAJEROVICE BINDING, ROUTE, SCHEMA, SEPAJEROVICE, USEOdyssey, TRAV4IGGE途乐, TYPE, PLAN GUIDE 和 NULL

[ @level0name = ] { 'level0_object_name' }

钦命的 0 级对象类型的名目。level0_object_name 的数据类型为 sysname,其私下认可值为 NULL。

[ @level1type = ] { 'level1_object_type' }

1 级对象的连串。level1_object_type 的数据类型为 varchar(128),其私下认可值为 NULL。有效的输入是 AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION 和 NULL。

[ @level1name = ] { 'level1_object_name' }

内定的 1 级对象类型的名号。level1_object_name 的数据类型为 sysname,其默许值为 NULL。

[ @level2type = ] { 'level2_object_type' }

2 级对象的门类。level2_object_type 的数据类型为 varchar(128),其默许值为 NULL。有效的输入是 COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETEOdyssey, TCRUISERIGGECRUISER 和 NULL。

[ @level2name = ] { 'level2_object_name' }

点名的 2 级对象类型的名号。level2_object_name 的数据类型为 sysname,其暗中同意值为 NULL。

再次回到值为0则成功,1则失利

USER 还是 SCHEMA

msdn 给出的文书档案里那样说:

The ability to specify USER as a level 0 type in an extended property of a level 1 type object will be removed in a future version of SQL Server. Use SCHEMA as the level 0 type instead. For example, when defining an extended property on a table, specify the schema of the table instead of a user name. The ability to specify TYPE as level-0 type will be removed in a future version of SQL Server. For TYPE, use SCHEMA as the level 0 type and TYPE as the level 1 type.

在等第1品类对象的扩展属性中将USE本田UR-V钦赐为等第0类型的效应就要现在版本的SQL Server中删除。 使用SCHEMA作为0级类型。 比如,在表上定义扩充属性时,请钦点表的方式,而不是客户名。 就要现在版本的SQL Server中除去将TYPE钦定为等第0类型的力量。 对于TYPE,使用SCHEMA作为0级类型,使用TYPE作为1级类型。

We do not recommend specifying USER as a level 0 type when you apply an extended property to a database object, because this can cause name resolution ambiguity. For example, assume user Mary owns two schemas (Mary and MySchema) and these schemas both contain a table named MyTable. If Mary adds an extended property to table MyTable and specifies @level0type = N'USER', @level0name = Mary, it is not clear to which table the extended property is applied. To maintain backward compatibility, SQL Server will apply the property to the table that is contained in the schema named Mary.

当将扩大属性应用于数据库对象时,大家不指出将USE传祺钦赐为等级0类型,因为那只怕会促成名称解析模糊。 比如,就算客商Mary具备七个情势(Mary和MySchema),这么些格局都包蕴一个名字为MyTable的表。 假若Mary向表MyTable增多增添属性并钦命@ level0type = N'USERubicon',@ level0name = Mary,则不清楚增添属性应用于哪个表。 为了保险向后宽容性,SQL Server会将该属性应用于含有在名称叫Mary的格局中的表。

计算来讲,当将扩展属性应用于数据库对象时,微软援用应用 SCHEMA,不推荐 USECRUISER,可是 USE福特Explorer照旧会保留,照旧得以应用不过在一些情形下(如上所述)或许会出现本身预想之外的专门的学业。

增添描述存款和储蓄进程【sp_addextendedproperty】语法

    sp_addextendedproperty  
        [ @name = ] { 'property_name' }  
        [ , [ @value = ] { 'value' }   
            [ , [ @level0type = ] { 'level0_object_type' }   
            , [ @level0name = ] { 'level0_object_name' }   
                    [ , [ @level1type = ] { 'level1_object_type' }   
                    , [ @level1name = ] { 'level1_object_name' }   
                            [ , [ @level2type = ] { 'level2_object_type' }   
                            , [ @level2name = ] { 'level2_object_name' }   
                            ]   
                    ]  
            ]   
        ]   
    [;] 

SqlServer创制数据表描述及列描述新闻

累积描述语法

履新描述

sp_updateextendedproperty  
    [ @name = ]{ 'property_name' }   
    [ , [ @value = ]{ 'value' }  
        [, [ @level0type = ]{ 'level0_object_type' }  
         , [ @level0name = ]{ 'level0_object_name' }  
              [, [ @level1type = ]{ 'level1_object_type' }  
               , [ @level1name = ]{ 'level1_object_name' }  
                     [, [ @level2type = ]{ 'level2_object_type' }  
                      , [ @level2name = ]{ 'level2_object_name' }  
                     ]  
              ]  
        ]  
    ]  

革新描述 和 增多的语法差不离,在那就不再赘述

Intro

Q: 为啥要创设描述消息?

A: 鼠标悬停在对应表和列上时,汇合世描述音讯,能够加强专门的工作的频率,依附工具大家依照数据表能够变动Model,能够将陈说消息。

威尼斯澳门在线 3

 

威尼斯澳门在线 4

 

In the end

小编水平有限,假诺开采有如何错误的地点,款待提出,应接与自家联系 ben121011@126.com

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:SqlServer创建数据表描述及列描述信息

关键词:

上一篇:【威尼斯澳门在线】mysql-2 mysql客商端

下一篇:关于binlog的那一点事