DB_EXTENDED_PROPERTY function [System]

Returns the value of the given property. Allows an optional property-specific string parameter to be specified.

Syntax

DB_EXTENDED_PROPERTY( 
{ property-id | property-name }
[, property-specific-argument
[, database-id | database-name ] ]
)

Parameters

property-id The database property ID to query.
property-name The database property name to query.
For a complete list of database properties, see Database properties.

property-specific-argument The following database properties allow you to specify additional arguments, as noted below, to return specific information about the property.

CharSet property Specify the name of a standard to obtain the default CHAR character set label for the standard. Possible values you can specify are: ASE, IANA, MIME, JAVA, WINDOWS, UTR22, IBM, and ICU. If no standard is specified, IANA is used as the default, unless the database connection was made through TDS, in which case ASE is the default.

CatalogCollation, Collation, and NcharCollation properties When querying these properties, the following values can be specified as a property-specific-argument to return information specific to the collation:

AccentSensitive Specify AccentSensitive to obtain the accent sensitivity setting for the collation. For example, the following statement returns the accent sensitivity setting for the NCHAR collation:

SELECT DB_EXTENDED_PROPERTY( 'NcharCollation', 'AccentSensitive');

Possible return values are: Ignore, Respect, and French. For a description of these values, see Collation tailoring options.

CaseSensitivity Specify CaseSensitivity to obtain the case sensitivity setting for the collation. Possible return values are: Ignore, Respect, UpperFirst, and LowerFirst. For a description of these values, see Collation tailoring options.
PunctuationSensitivity Specify PunctuationSensitivity to obtain the punctuation sensitivity setting for the collation. Possible return values are: Ignore, Primary, and Quaternary. For a description of these values, see Collation tailoring options.
Properties Specify Properties to obtain a string containing all the tailoring options specified for the collation. For a description of the keywords and values in the returned string, see Collation tailoring options.
Specification Specify Specification to obtain a string containing the full collation specification used for the collation. For a description of the keywords and values in the returned string, see Collation tailoring options.

DriveType property Specify the name of a dbspace, or the file ID for the dbspace, to obtain its drive type. The value returned is one of the following: CD, FIXED, RAMDISK, REMOTE, REMOVABLE, or UNKNOWN. If nothing is specified, the drive type of the system dbspace is returned. If the specified dbspace doesn't exist, the property function returns NULL. If the name of a dbspace is specified and the ID of a database that isn't the database of the current connection is also specified, the function also returns NULL.
File property Specify a dbspace name to obtain the file name of the database root file, including the path. If nothing is specified, information for the system dbspace is returned. If the specified file doesn't exist, the function returns NULL.
FileSize property Specify the name of a dbspace, or the file ID for the dbspace, to obtain the size of the specified file. You can also specify temporary to return the size of the temporary dbspace, or translog to return the size of the log file. If nothing is specified, the size of the system dbspace is returned. If the specified file doesn't exist, the function returns NULL.
FreePages property Specify the name of a dbspace, or the file ID for the dbspace, to obtain the number of free pages. You can also specify temporary to return the number of free pages in the temporary dbspace, or translog to return the number of free pages in the log file. If nothing is specified, the number of free pages in the system dbspace is returned. If the specified file doesn't exist, the function returns NULL.
IOParallelism property Specify a dbspace name to obtain the estimated number of simultaneous I/O operations supported by the dbspace. If a dbspace is not specified, the current system dbspace is used.
NextScheduleTime property Specify an event name to obtain its next scheduled execution time.

database-id The database ID number, as returned by the DB_ID function. Typically, the database name is used.
database-name The name of the database, as returned by the DB_NAME function.

Returns

Returns a VARCHAR value. The current database is used if the third argument is omitted.

Remarks

The DB_EXTENDED_PROPERTY function is similar to the DB_PROPERTY function except that it allows an optional property-specific-argument string parameter to be specified. The interpretation of property-specific-argument depends on the property ID or name specified in the first argument.

When comparing catalog strings such as table names and procedure names, the database server uses the CHAR collation. For the UCA collation, the catalog collation is the same as the CHAR collation but with the tailoring changed to be case-insensitive, accent-insensitive and with punctuation sorted in the primary level. For legacy collations, the catalog collation is the same as the CHAR collation but with the tailoring changed to be case-insensitive. While you cannot explicitly specify the tailoring used for the catalog collation, you can query the Specification property to obtain the full collation specification used by the database server for comparing catalog strings. Querying the Specification property can be useful if you need to exploit the difference between the CHAR and catalog collations. For example, suppose you have a punctuation-insensitive CHAR collation and you want to execute an upgrade script that defines a procedure called my_procedure, and that also attempts to delete an old version named myprocedure. The following statements cannot achieve the desired results because my_procedure is equivalent to myprocedure, using the CHAR collation:

CREATE PROCEDURE my_procedure( ) ...; 
IF EXISTS ( SELECT * FROM SYS.SYSPROCEDURE WHERE proc_name = 'myprocedure' ) 
THEN DROP PROCEDURE myprocedure 
END IF;

Instead, you could execute the following statements to achieve the desired results:

CREATE PROCEDURE my_procedure( ) ...; 
IF EXISTS ( SELECT * FROM SYS.SYSPROCEDURE 
   WHERE COMPARE( proc_name, 'myprocedure', DB_EXTENDED_PROPERTY( 'CatalogCollation', 'Specification' ) ) = 0 ) 
THEN DROP PROCEDURE myprocedure 
END IF;

Standards and compatibility

SQL/2003 Vendor extension.

Example

The following statement returns the location of the current database:

SELECT DB_EXTENDED_PROPERTY( 'File' );

The following statement returns the file size of the system dbspace, in pages.

SELECT DB_EXTENDED_PROPERTY( 'FileSize' );

The following statement returns the file size of the transaction log, in pages.

SELECT DB_EXTENDED_PROPERTY( 'FileSize', 'translog' );

The following statement returns the case sensitivity setting for the NCHAR collation:

SELECT DB_EXTENDED_PROPERTY( 'NcharCollation',' CaseSensitivity' );

The following statement returns the tailoring options specified for the database CHAR collation:

SELECT DB_EXTENDED_PROPERTY ( 'Collation', 'Properties' );

The following statement returns the full collation specification for the database NCHAR collation:

SELECT DB_EXTENDED_PROPERTY( 'NCharCollation', 'Specification' );

Discuss this page in DocCommentXchange.
Send feedback about this page using email.