如何在sql server中将对象（表）的标题添加到其名称旁边

I have multiple database objects in my database constituting tables, views, functions, sp & triggers. They are distributed among different layer of schemas.

我的数据库中有多个数据库对象,构成表,视图,函数,sp和触发器。它们分布在不同的模式层中。

For the sake of better documentation, I want to codename them like db001_sch01_obj_01, so I want to add these caption names while creating objects. Is there a way to add an captain to db objects apart from obvious name.

为了更好的文档,我想将它们像db001_sch01_obj_01一样代号,所以我想在创建对象时添加这些标题名称。有没有办法将队长添加到数据库对象,而不是明显的名称。

1 个解决方案

#1

Extended Properties is a feature that should serve your purpose. The examples on the docs site even have the hierarchy you are looking to implement:

扩展属性是一个应该满足您的目的的功能。 docs站点上的示例甚至具有您要实现的层次结构:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addextendedproperty-transact-sql

You can use the stored procedure sp-addextendedproperty or you can manually add or update the properties of each object by right clicking them individually and going to Properties>Extended Properties.

您可以使用存储过程sp-addextendedproperty,也可以通过单独右键单击并转到“属性”>“扩展属性”来手动添加或更新每个对象的属性。

Example Syntax:

USE AdventureWorks2012;  
GO  
EXEC sp_addextendedproperty   
@name = N'Caption',   
@value = 'Postal code is a required column.',  
@level0type = N'Schema', @level0name = 'Person',  
@level1type = N'Table',  @level1name = 'Address',  
@level2type = N'Column', @level2name = 'PostalCode';  
GO

Remarks:

For specifying extended properties, the objects in a SQL Server database are classified into three levels: 0, 1, and 2. Level 0 is the highest level and is defined as objects that are contained at the database scope. Level 1 objects are contained in a schema or user scope, and level 2 objects are contained by level 1 objects. Extended properties can be defined for objects at any of these levels.

为了指定扩展属性,SQL Server数据库中的对象分为三个级别:0,1和2.级别0是*别,定义为数据库作用域中包含的对象。 1级对象包含在模式或用户范围中,2级对象包含在1级对象中。可以为任何这些级别的对象定义扩展属性。

References to an object in one level must be qualified with the names of the higher level objects that own or contain them. For example, when you add an extended property to a table column (level 2), you must also specify the table name (level 1) that contains the column and the schema (level 0) that contains the table. +

必须使用拥有或包含它们的更高级别对象的名称限定对一个级别中对象的引用。例如,将扩展属性添加到表列(级别2)时,还必须指定包含列的表名(级别1)和包含该表的架构(级别0)。 +

If all object types and names are null, the property belongs to the current database itself.

如果所有对象类型和名称都为null,则该属性属于当前数据库本身。

Extended properties are not allowed on system objects, objects outside the scope of a user-defined database, or objects not listed in Arguments as valid inputs.

系统对象,用户定义数据库范围之外的对象或未在Arguments中列为有效输入的对象不允许使用扩展属性。

Extended properties are not allowed on memory-optimized tables.

内存优化表上不允许使用扩展属性。

#1