sp_addextendedproperty (Transact-SQL) - SQL Server (2023)

  • Article
  • 6 minutes to read

Applies to: sp_addextendedproperty (Transact-SQL) - SQL Server (1) SQL Server (all supported versions) sp_addextendedproperty (Transact-SQL) - SQL Server (2) Azure SQL Database sp_addextendedproperty (Transact-SQL) - SQL Server (3) Azure SQL Managed Instance

Adds a new extended property to a database object.
Security

sp_addextendedproperty (Transact-SQL) - SQL Server (4) Transact-SQL Syntax Conventions

Syntax

 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' } ] ] ] ] [;] 

Arguments

[ @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.

(Video) sp depends in SQL Server

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.

Important

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.

[ @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, SEQUENCE, 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.

(Video) Databases: How can I detect the availability of sp_addextendedproperty on sql server?

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

Return Code Values

0 (success) or 1 (failure)

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.

(Video) SQL Server Extended Properties как простейший способ докум

Replicating Extended Properties

Extended properties are replicated only in the initial synchronization between the Publisher and the Subscriber. If you add or modify an extended property after the initial synchronization, the change is not replicated. For more information about how to replicate database objects, see Publish Data and Database Objects.

Schema vs. User

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.

Permissions

Members of the db_owner and db_ddladmin fixed database roles can add extended properties to any object with the following exception: db_ddladmin cannot add properties to the database itself, or to users or roles.

Users can add extended properties to objects they own or have ALTER or CONTROL permissions on.

Examples

A. Adding an extended property to a database

The following example adds the property name 'Caption' with a value of 'AdventureWorks2012 Sample OLTP Database' to the AdventureWorks2012 sample database.

USE AdventureWorks2012; GO --Add a caption to the AdventureWorks2012 Database object itself. EXEC sp_addextendedproperty @name = N'Caption', @value = 'AdventureWorks2012 Sample OLTP Database'; 

B. Adding an extended property to a column in a table

The following example adds a caption property to column PostalCode in table Address.

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 

C. Adding an input mask property to a column

The following example adds an input mask property '99999 or 99999-9999 or #### ###' to the column PostalCode in the table Address.

(Video) T SQL's Hidden Support Feature Jennifer McCown

USE AdventureWorks2012; GO EXEC sp_addextendedproperty @name = N'Input Mask ', @value = '99999 or 99999-9999 or #### ###', @level0type = N'Schema', @level0name = 'Person', @level1type = N'Table', @level1name = 'Address', @level2type = N'Column',@level2name = 'PostalCode'; GO 

D. Adding an extended property to a filegroup

The following example adds an extended property to the PRIMARY filegroup.

USE AdventureWorks2012; GO EXEC sys.sp_addextendedproperty @name = N'MS_DescriptionExample', @value = N'Primary filegroup for the AdventureWorks2012 sample database.', @level0type = N'FILEGROUP', @level0name = 'PRIMARY'; GO 

E. Adding an extended property to a schema

The following example adds an extended property to the HumanResources schema.

USE AdventureWorks2012; GO EXECUTE sys.sp_addextendedproperty @name = N'MS_DescriptionExample', @value = N'Contains objects related to employees and departments.', @level0type = N'SCHEMA', @level0name = 'HumanResources'; 

F. Adding an extended property to a table

The following example adds an extended property to the Address table in the Person schema.

USE AdventureWorks2012; GO EXEC sys.sp_addextendedproperty @name = N'MS_DescriptionExample', @value = N'Street address information for customers, employees, and vendors.', @level0type = N'SCHEMA', @level0name = 'Person', @level1type = N'TABLE', @level1name = 'Address'; GO 

G. Adding an extended property to a role

The following example creates an application role and adds an extended property to the role.

USE AdventureWorks2012; GO CREATE APPLICATION ROLE Buyers WITH Password = '987G^bv876sPY)Y5m23'; GO EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Application Role for the Purchasing Department.', @level0type = N'USER', @level0name = 'Buyers'; 

H. Adding an extended property to a type

The following example adds an extended property to a type.

USE AdventureWorks2012; GO EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Data type (alias) to use for any column that represents an order number. For example a sales order number or purchase order number.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TYPE', @level1name = N'OrderNumber'; 

I. Adding an extended property to a user

The following example creates a user and adds an extended property to the user.

USE AdventureWorks2012; GO CREATE USER CustomApp WITHOUT LOGIN ; GO EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'User for an application.', @level0type = N'USER', @level0name = N'CustomApp'; 

See Also

Database Engine Stored Procedures (Transact-SQL)
sys.fn_listextendedproperty (Transact-SQL)
sp_dropextendedproperty (Transact-SQL)
sp_updateextendedproperty (Transact-SQL)

(Video) T-SQL's Hidden Support Feature

FAQs

What is Sp_addextendedproperty in SQL Server? ›

Sp_addextendedproperty stored procedure is used to create metadata about our database objects. Using this stored procedure we can put information about Sql server objects like name of developer who created the object, to track the version of objects inside database.

What is MS_Description extended property? ›

Microsoft provides one extended property, MS_Description, which can be used from both Enterprise Manager and SSMS to provide a description of the object to which it is bound.

How do I drop an extended property? ›

Right click on Table/View in Object Explorer, select Properties. Select Extended Properties from the pane on the left. Select the extended properties and click Delete at the bottom right of the window.

What is extended properties in SQL Server? ›

Extended Properties in SQL Server allows us to create additional customized properties to store additional information. Extended Properties are a way to create a self-documenting database. Every Extended Property has a user defined name and value.

What is Sql_variant data type? ›

The sql_variant data type allows a table column or a variable to hold values of any data type with a maximum length of 8000 bytes plus 16 bytes that holds the data type information, but there are exceptions as noted below. This allows you to have flexibility in the type of data that is stored.

What is extended database? ›

Extend and Unextend a Database. You can extend a database to use additional data and work locations. Locations must exist prior to this operation and must be specified with a Usage Type of database or work. To extend a database, use the extenddb command or the Alter Database dialog in Director or VDBA.

Videos

1. Databases: How can a database parameter be used on `sp_addextendedproperty`?
(Roel Van de Paar)
2. T SQL's Hidden Support Feature Jennifer McCown
(DBAFundamentals)
3. Tips and Tricks for Using SQL Server Management Studio Effectively
(DBAFundamentals)
4. Importing SQL DB Definitions to PBIX
(Kevin Arnold)
5. Uses of sys.sysdatabases in SQL Server
(C Plus+)
6. SQL Server DBA Tutorial 25 - How to Configure Distribution in SQL Server Replication
(Chirags Tutorial)
Top Articles
Latest Posts
Article information

Author: Arline Emard IV

Last Updated: 02/26/2023

Views: 6702

Rating: 4.1 / 5 (72 voted)

Reviews: 87% of readers found this page helpful

Author information

Name: Arline Emard IV

Birthday: 1996-07-10

Address: 8912 Hintz Shore, West Louie, AZ 69363-0747

Phone: +13454700762376

Job: Administration Technician

Hobby: Paintball, Horseback riding, Cycling, Running, Macrame, Playing musical instruments, Soapmaking

Introduction: My name is Arline Emard IV, I am a cheerful, gorgeous, colorful, joyous, excited, super, inquisitive person who loves writing and wants to share my knowledge and understanding with you.