About extended properties for database objects

Today, someone asked if there was any way to view the comments on the table, or to query all the tables. Comments about tables or table fields, etc., are actually extended properties of the database object. In MSSQL, it is supported to put some annotative content into the database or database object, enhance the readability, and help the management and maintenance work in the future. The contents of an extended property can be added, modified, or deleted through SSMs, or can be maintained by executing related stored procedures through a system view query.

Create a test table:

IF object_id(N'T8') is  not NULLBEGIN    DROP TABLET8ENDGOCREATE TABLET8 (IDINT  not NULL, nameNVARCHAR( -))GO

Code-1

To add extended properties for a table: Locate the newly created table in the Object Explorer and right-click the property.

Figure-1

Click Extended properties to add, modify, and delete.

Figure-2

Add extended properties for a field.

Figure-3

Field Properties--description, add comment content.

Figure-4

Once saved, you can complete the addition of the field extension properties. Queries can be made through System view sys.extended_properties.

SELECT *,object_name as from sys.extended_properties

Code-2

As you can see, the attributes you just added on SSMs have been queried. The default extended property name is Ms_description.

Figure-5

System View Sys.extended_properties A detailed description of each field, which can be consulted on SQL online from the book. In addition to the system view, you can also query by function Fn_listextendedproperty.

SELECTObjType, objname, name, value fromFn_listextendedproperty (default,'SCHEMA','dbo','TABLE','T8',default,default);SELECTObjtype,objname,name,value fromFn_listextendedproperty (default,'SCHEMA','dbo','TABLE','T8','COLUMN','ID');SELECTObjtype,objname,name,value fromFn_listextendedproperty (default,'SCHEMA','dbo','TABLE','T8','COLUMN','name');

Code-3

Figure-6

Extended properties can be maintained using related stored procedures. Then execute the CODE-1 code, rebuild the test table, and the related properties will be deleted. Execute the stored procedure sp_addextendedproperty to add. The parameters of the stored procedure are used, consult the documentation, and provide links at the end of this article.

EXECsp_addextendedproperty@name =N'ms_description',@value =N'This was a table description on [T8] (2).',@level0type =N'SCHEMA',@level0name =N'dbo',@level1type =N'TABLE',@level1name =N'T8'GOEXECsp_addextendedproperty@name =N'ms_description',@value =N'This was a column description on [ID] (2).',@level0type =N'SCHEMA',@level0name =N'dbo',@level1type =N'TABLE',@level1name =N'T8',@level2type =N'COLUMN',@level2name =N'ID'GOEXECsp_addextendedproperty@name =N'ms_description',@value =N'This was a column description on [name] (2).',@level0type =N'SCHEMA',@level0name =N'dbo',@level1type =N'TABLE',@level1name =N'T8',@level2type =N'COLUMN',@level2name =N'name'GO

Code-4

Query Sys.extended_properties, the extended properties of tables and fields have been added successfully.

Figure-7

Executes Sp_dropextendedproperty deletes an existing extended property.

EXECSp_dropextendedproperty@name =N'ms_description',@level0type =N'SCHEMA',@level0name =N'dbo',@level1type =N'TABLE',@level1name =N'T8',@level2type =N'COLUMN',@level2name =N'name'GO

Code-5

After querying Sys.extended_properties, the extended property of the field name has been deleted.

Figure-8

Use Sp_updateextendedproperty to update extended properties.

EXECSp_updateextendedproperty@name =N'ms_description',@value =N'This was a column description on [ID] (3).',@level0type =N'SCHEMA',@level0name =N'dbo',@level1type =N'TABLE',@level1name =N'T8',@level2type =N'COLUMN',@level2name =N'ID'GO

Code-6

Figure-9

Not only can the table add extended attributes, other database objects can also, such as databases, indexes, and so on.

 Use adventureworks2008r2; GO SELECT *,object_name as from sys.extended_propertiesGO

Code-7

Figure-10

Figure-11

Reference Documentation:

To use extended properties on database objects:

Https://technet.microsoft.com/zh-cn/library/ms190243%28v=sql.105%29.aspx

To view extended properties:

Https://technet.microsoft.com/zh-cn/library/ms186989%28v=sql.105%29.aspx

Sys.extended_properties:

Https://technet.microsoft.com/zh-cn/library/ms177541%28v=sql.105%29.aspx

Sp_addextendedproperty:

Https://technet.microsoft.com/zh-cn/library/ms180047%28v=sql.105%29.aspx

Sp_dropextendedproperty:

Https://technet.microsoft.com/zh-cn/library/ms178595%28v=sql.105%29.aspx

Sp_updateextendedproperty:

Https://technet.microsoft.com/zh-cn/library/ms186885%28v=sql.105%29.aspx

Fn_listextendedproperty:

Https://technet.microsoft.com/zh-cn/library/ms179853%28v=sql.105%29.aspx

About extended properties for database objects