CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-design
Cellman
Cellman

Reputation: 135

How to add a caption to object (table) aside its name in sql server

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

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.

Upvotes: 0

Views: 1680

Answers (1)

Jacob H
Jacob H

Reputation: 2505

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:

https://learn.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.

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.

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. +

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

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.

Extended properties are not allowed on memory-optimized tables.

Upvotes: 2

Related Questions