Export (0) Print
Expand All

MDSCHEMA_PROPERTIES Rowset

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Describes the properties of members within a database.

The MDSCHEMA_PROPERTIES rowset contains the following columns.

Column name

Type indicator

Length

Description

CATALOG_NAME

DBTYPE_WSTR

The name of the database.

SCHEMA_NAME

DBTYPE_WSTR

The name of the schema to which this property belongs. NULL if the provider does not support schemas.

CUBE_NAME

DBTYPE_WSTR

The name of the cube.

DIMENSION_UNIQUE_NAME

DBTYPE_WSTR

The unique name of the dimension. For providers that generate unique names by qualification, each component of this name is delimited.

HIERARCHY_UNIQUE_NAME

DBTYPE_WSTR

The unique name of the hierarchy. For providers that generate unique names by qualification, each component of this name is delimited.

LEVEL_UNIQUE_NAME

DBTYPE_WSTR

The unique name of the level to which this property belongs. If the provider does not support named levels, it should return the DIMENSION_UNIQUE_NAME value for this field. For providers that generate unique names by qualification, each component of this name is delimited.

MEMBER_UNIQUE_NAME

DBTYPE_WSTR

The unique name of the member to which the property belongs. Used for data stores that do not support named levels or have properties on a member-by-member basis. If the property applies to all members in a level, this column is NULL. For providers that generate unique names by qualification, each component of this name is delimited.

PROPERTY_TYPE

DBTYPE_I2

A bitmap that specifies the type of the property:

  • MDPROP_MEMBER (1) identifies a property of a member. This property can be used in the DIMENSION PROPERTIES clause of the SELECT statement.

  • MDPROP_CELL (2) identifies a property of a cell. This property can be used in the CELL PROPERTIES clause that occurs at the end of the SELECT statement.

  • MDPROP_SYSTEM (4) identifies an internal property.

  • MDPROP_BLOB (8) identifies a property which contains a binary large object (blob).

PROPERTY_NAME

DBTYPE_WSTR

The name of the property. If the key for the property is the same as the name for the property, PROPERTY_NAME will be blank.

PROPERTY_CAPTION

DBTYPE_WSTR

A label or caption associated with the property, used primarily for display purposes. Returns PROPERTY_NAME if a caption does not exist.

DATA_TYPE

DBTYPE_UI2

The data type of the property.

CHARACTER_MAXIMUM_LENGTH

DBTYPE_UI4

The maximum possible length of the property, if it is a character, binary, or bit type.

Zero indicates there is no defined maximum length.

Returns NULL for all other data types.

CHARACTER_OCTET_LENGTH

DBTYPE_UI4

The maximum possible length (in bytes) of the property, if it is a character or binary type.

Zero indicates there is no defined maximum length.

Returns NULL for all other data types.

NUMERIC_PRECISION

DBTYPE_UI2

The maximum precision of the property, if it is a numeric data type.

Returns NULL for all other data types.

NUMERIC_SCALE

DBTYPE_I2

The number of digits to the right of the decimal point, if it is a DBTYPE_NUMERIC or DBTYPE_DECIMAL type.

Returns NULL for all other data types.

DESCRIPTION

DBTYPE_WSTR

A human readable description of the property. NULL if no description exists.

PROPERTY_CONTENT_TYPE

DBTYPE_I2

The type of the property. Can be one of the following enumerations:

  • MD_PROPTYPE_REGULAR (0x00)

  • MD_PROPTYPE_ID (0x01)

  • MD_PROPTYPE_RELATION_TO_PARENT (0x02)

  • MD_PROPTYPE_ROLLUP_OPERATOR (0x03)

  • MD_PROPTYPE_ORG_TITLE (0x11)

  • MD_PROPTYPE_CAPTION (0x21)

  • MD_PROPTYPE_CAPTION_SHORT (0x22)

  • MD_PROPTYPE_CAPTION_DESCRIPTION (0x23)

  • MD_PROPTYPE_CAPTION_ABREVIATION (0x24)

  • MD_PROPTYPE_WEB_URL (0x31)

  • MD_PROPTYPE_WEB_HTML (0x32)

  • MD_PROPTYPE_WEB_XML_OR_XSL (0x33)

  • MD_PROPTYPE_WEB_MAIL_ALIAS (0x34)

  • MD_PROPTYPE_ADDRESS (0x41)

  • MD_PROPTYPE_ADDRESS_STREET (0x42)

  • MD_PROPTYPE_ADDRESS_HOUSE (0x43)

  • MD_PROPTYPE_ADDRESS_CITY (0x44)

  • MD_PROPTYPE_ADDRESS_STATE_OR_PROVINCE (0x45)

  • MD_PROPTYPE_ADDRESS_ZIP (0x46)

  • MD_PROPTYPE_ADDRESS_QUARTER (0x47)

  • MD_PROPTYPE_ADDRESS_COUNTRY (0x48)

  • MD_PROPTYPE_ADDRESS_BUILDING (0x49)

  • MD_PROPTYPE_ADDRESS_ROOM (0x4A)

  • MD_PROPTYPE_ADDRESS_FLOOR (0x4B)

  • MD_PROPTYPE_ADDRESS_FAX (0x4C)

  • MD_PROPTYPE_ADDRESS_PHONE (0x4D)

  • MD_PROPTYPE_GEO_CENTROID_X (0x61)

  • MD_PROPTYPE_GEO_CENTROID_Y (0x62)

  • MD_PROPTYPE_GEO_CENTROID_Z (0x63)

  • MD_PROPTYPE_GEO_BOUNDARY_TOP (0x64)

  • MD_PROPTYPE_GEO_BOUNDARY_LEFT (0x65)

  • MD_PROPTYPE_GEO_BOUNDARY_BOTTOM (0x66)

  • MD_PROPTYPE_GEO_BOUNDARY_RIGHT (0x67)

  • MD_PROPTYPE_GEO_BOUNDARY_FRONT (0x68)

  • MD_PROPTYPE_GEO_BOUNDARY_REAR (0x69)

  • MD_PROPTYPE_GEO_BOUNDARY_POLYGON (0x6A)

  • MD_PROPTYPE_PHYSICAL_SIZE (0x71)

  • MD_PROPTYPE_PHYSICAL_COLOR (0x72)

  • MD_PROPTYPE_PHYSICAL_WEIGHT (0x73)

  • MD_PROPTYPE_PHYSICAL_HEIGHT (0x74)

  • MD_PROPTYPE_PHYSICAL_WIDTH (0x75)

  • MD_PROPTYPE_PHYSICAL_DEPTH (0x76)

  • MD_PROPTYPE_PHYSICAL_VOLUME (0x77)

  • MD_PROPTYPE_PHYSICAL_DENSITY (0x78)

  • MD_PROPTYPE_PERSON_FULL_NAME (0x82)

  • MD_PROPTYPE_PERSON_FIRST_NAME (0x83)

  • MD_PROPTYPE_PERSON_LAST_NAME (0x84)

  • MD_PROPTYPE_PERSON_MIDDLE_NAME (0x85)

  • MD_PROPTYPE_PERSON_DEMOGRAPHIC (0x86)

  • MD_PROPTYPE_PERSON_CONTACT (0x87)

  • MD_PROPTYPE_QTY_RANGE_LOW (0x91)

  • MD_PROPTYPE_QTY_RANGE_HIGH (0x92)

  • MD_PROPTYPE_FORMATTING_COLOR (0xA1)

  • MD_PROPTYPE_FORMATTING_ORDER (0xA2)

  • MD_PROPTYPE_FORMATTING_FONT (0xA3)

  • MD_PROPTYPE_FORMATTING_FONT_EFFECTS (0xA4)

  • MD_PROPTYPE_FORMATTING_FONT_SIZE (0xA5)

  • MD_PROPTYPE_FORMATTING_SUB_TOTAL (0xA6)

  • MD_PROPTYPE_DATE (0xB1)

  • MD_PROPTYPE_DATE_START (0xB2)

  • MD_PROPTYPE_DATE_ENDED (0xB3)

  • MD_PROPTYPE_DATE_CANCELED (0xB4)

  • MD_PROPTYPE_DATE_MODIFIED (0xB5)

  • MD_PROPTYPE_DATE_DURATION (0xB6)

  • MD_PROPTYPE_VERSION (0xC1)

SQL_COLUMN_NAME

DBTYPE_WSTR

The name of the property used in SQL queries from the cube dimension or database dDimension.

LANGUAGE

DBTYPE_UI2

The translation expressed as an LCID. Only valid for property translations.

PROPERTY_ORIGIN

DBTYPE_UI2

Identifies the type of hierarchy that the property applies to:

  • MD_USER_DEFINED (1) indicates the property is on a user defined hierarchy

  • MD_SYSTEM_ENABLED (2) indicates the property is on an attribute hierarchy

  • MD_SYSTEM_DISABLED (4) indicates the property is on an attribute hierarchy that is not enabled.

PROPERTY_ATTRIBUTE_HIERARCHY_NAME

DBTYPE_WSTR

The name of the attribute hierarchy sourcing this property.

PROPERTY_CARDINALITY

DBTYPE_WSTR

The cardinality of the property. Possible values include the following strings:

  • ONE

  • MANY

MIME_TYPE

DBTYPE_WSTR

The mime type for binary large objects (BLOBs).

PROPERTY_IS_VISIBLE

DBTYPE_BOOL

A Boolean that indicates whether the property is visible.

TRUE if the property is visible; otherwise, FALSE.

This schema rowset is not sorted.

The MDSCHEMA_PROPERTIES rowset can be restricted on the columns listed in the following table.

Column name

Type indicator

Restriction State

CATALOG_NAME

DBTYPE_WSTR

Mandatory

SCHEMA_NAME

DBTYPE_WSTR

Optional

CUBE_NAME

DBTYPE_WSTR

Optional

DIMENSION_UNIQUE_NAME

DBTYPE_WSTR

Optional

HIERARCHY_UNIQUE_NAME

DBTYPE_WSTR

Optional

LEVEL_UNIQUE_NAME

DBTYPE_WSTR

Optional

MEMBER_UNIQUE_NAME

DBTYPE_WSTR

Optional

PROPERTY_TYPE

DBTYPE_I2

Optional

PROPERTY_NAME

DBTYPE_WSTR

Optional

PROPERTY_CONTENT_TYPE

DBTYPE_I2

(Optional) A default restriction is in place on MDPROP_MEMBER OR MDPROP_CELL.

PROPERTY_ORIGIN

DBTYPE_UI2

(Optional) A default restriction is in place on MD_USER_DEFINED OR MD_SYSTEM_ENABLED.

CUBE_SOURCE

DBTYPE_UI2

(Optional) A bitmap with one of the following valid values:

  • 1 CUBE

  • 2 DIMENSION

Default restriction is a value of 1.

PROPERTY_VISIBILITY

DBTYPE_UI2

(Optional) A bitmap with one of the following valid values:

  • 1 Visible

  • 2 Not visible

Default restriction is a value of 1.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft