Obtain the description of the sqlserver Field

Obtain the description of the sqlserver Field
Fn_listextendedproperty
Returns the extended attribute value of the database object.

 

Syntax
Fn_listextendedproperty (
{Default | [@ name =] 'Property _ name' | null}
, {Default | [@ level0type =] 'vel0 _ object_type '| null}
, {Default | [@ level0name =] 'vel0 _ object_name '| null}
, {Default | [@ level1type =] 'vel1 _ object_type '| null}
, {Default | [@ level1name =] 'vel1 _ object_name '| null}
, {Default | [@ level2type =] 'vel2 _ object_type '| null}
, {Default | [@ level2name =] 'vel2 _ object_name '| null}
)

Parameters
{Default | [@ name =] 'Property _ name' | null}

Is the property name. Property_name is of the sysname type. Valid input is default, null, or attribute name.

{Default | [@ level0type =] 'vel0 _ object_type '| null}

User or user-defined type. The data type of level0_object_type is varchar (128), and its default value is null. Valid inputs include user, type, default, and null.

{Default | [@ level0name =] 'vel0 _ object_name' | null}

The name of the specified level 0 object type. The data type of level0_object_name is sysname, and its default value is null. Valid input is default, null, or object name.

{Default | [@ level1type =] 'vel1 _ object_type '| null}

Type of level 1 objects. The data type of level1_object_type is varchar (128), and its default value is null. Valid inputs include table, view, procedure, function, default, rule, default, and null.

 

The default ing is null, while the "default" ing object type is default.

{Default | [@ level1name =] 'vel1 _ object_name' | null}

The name of the specified level 1 object type. The data type of level1_object_name is sysname, and its default value is null. Valid input is default, null, or object name.

{Default | [@ level2type =] 'vel2 _ object_type '| null}

Type of level 2 objects. The data type of level2_object_type is varchar (128), and its default value is null. Valid inputs include column, parameter, index, constraint, trigger, default, default (ing null), and null.

{Default | [@ level2name =] 'vel2 _ object_name' | null}

The name of the specified level 2 object type. The data type of level2_object_name is sysname, and its default value is null. Valid input is default, null, or object name.

Return table
The format of the table returned by fn_listextendedproperty is as follows.

Column Name Data Type
Objtype sysname
Objname sysname
Name sysname
Value SQL _variant

If the returned table is empty, the object may not have extended attributes or the user does not have permission to list extended attributes of the object.

Note
System Objects cannot have extended attributes.

If the value of property_name is null or default, fn_listextendedproperty returns all attributes of the object.

If the object type is specified and the value of the corresponding object name is null or default, fn_listextendedproperty returns all extended attributes of all objects of the specified type.

Objects are classified by level. The value 0 indicates the highest level, and the value 2 indicates the lowest level. If a lower-level object (level 1 or Level 2) Type and name are specified, the value of the parent object type and name cannot be null or default. Otherwise, the function returns an error.

Permissions used to list extended attributes of certain object types are different.

For objects with a level 0, if a user is the user identified in the name of level 0, or a member of the db_owner and db_ddladmin fixed database roles, this user can list the extended attributes of the specified "user" type.

All users can use level 0 object type "type" to list extended attributes.

For a level-1 object, if the user is the object owner or the user has permissions on the object, the user can use any valid type value to list the extended attributes.

For level 2 objects, if the current user has any permissions on the parent object (level 1 and level 0), the user can use any valid type value to list extended attributes.
Example
This example lists all the extended attributes of the database.

Select *
From: fn_listextendedproperty (null, null)

-Or-

Select *
From: fn_listextendedproperty (default, default)

This example lists all the extended attributes of all columns in table "T1.

Create Table T1 (ID int, name char (20 ))

Exec sp_addextendedproperty 'caption ', 'employee id', 'user', DBO, 'table', 't1', 'column', ID

Exec sp_addextendedproperty 'caption ', 'employee name', 'user', DBO, 'table', 't1', 'column', name

Select *
From: fn_listextendedproperty (null, 'user', 'dbo', 'table', 't1', 'column', default)

The following is the result set:

Objtype objname name value
Column ID caption employee ID
Column name caption employee n