DB_EXTENDED_PROPERTY function [System]

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

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

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 more information, 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 more information, 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 more information, see Collation tailoring options.
Properties Specify Properties to obtain a string containing all the tailoring options specified for the collation. For information about 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 information about 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 in pages. 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.
MirrorServerState property Specify a server name to determine the connection status of the mirror server. Returns CONNECTED, DISCONNECTED, INCOMING ONLY, OUTGOING ONLY, or NULL.
MirrorState property Specify a server name to determine the synchronization status of the mirror server. Returns SYNCHRONIZING, SYNCHRONIZED, or NULL.
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.

VARCHAR

Cloud note: Because of tenant database isolation rules, when this function is called in the cloud it returns only information about the current tenant database.

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.

The current database is used if the third argument is omitted.

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;

SQL/2008 Vendor extension.

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' );

The following statement returns the connection status of the mirror server Test:

SELECT DB_EXTENDED_PROPERTY( 'MirrorServerState', 'Test' );

The following statement returns the synchronization status of the mirror server Test:

SELECT DB_EXTENDED_PROPERTY( 'MirrorState', 'Test' );

DB_EXTENDED_PROPERTY function [System]

Syntax

Parameters

Returns

Remarks

See also

Standards and compatibility

Example