Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL functions » Functions

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.

  • property-specific-argument

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

    • CatalogCollation, Collation, NcharCollation

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

      • AccentSensitivity

        Returns 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', 'AccentSensitivity');

        Possible return values are: NULL, Ignore, Respect, and French.

      • CaseSensitivity

        Returns the case sensitivity setting for the collation. Possible return values are: NULL, Ignore, Respect, UpperFirst, and LowerFirst.

      • PunctuationSensitivity

        Returns the punctuation sensitivity setting for the collation. Possible return values are: NULL, Ignore, Primary, and Quaternary.

      • Properties

        Returns a string containing all the tailoring options specified for the collation.

      • Specification

        Returns a string containing the full collation specification used for the collation.

    • CharSet

      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.

    • DBFileFragments

      Specify the name of a dbspace, or the file ID for the dbspace, to obtain the number of file fragments. If you do not specify the dbspace name or file ID, then the system dbspace is used. If the specified dbspace name or ID does not exist for the database to which you are connected, then the function returns NULL.

    • DriveBus

      (Microsoft Windows only) Specify the name of a dbspace, or the file ID for the dbspace, to obtain the configuration of the drive on which it resides. DriveBus returns BusType from an IOCTL_STORAGE_QUERY_PROPERTY call. If you do not specify the dbspace name or file ID, then the system dbspace is used. If the specified dbspace name or ID does not exist for the database to which you are connected, then the function returns NULL.

    • DriveModel

      (Microsoft Windows only) Specify the name of a dbspace, or the file ID for the dbspace, to obtain the model of the drive on which it resides. DriveModel returns the concatenation of the VendorId, ProductId, and ProductRevision strings from an IOCTL_STORAGE_QUERY_PROPERTY call. If you do not specify the dbspace name or file ID, then the system dbspace is used. If the specified dbspace name or ID does not exist for the database to which you are connected, then the function returns NULL.

    • DriveType

      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 you do not specify the dbspace name or file ID, then the system dbspace is used. If the specified dbspace name or ID does not exist for the database to which you are connected, then the function returns NULL.

    • File

      Specify the name of a dbspace, or the file ID for the dbspace, to obtain the file name of the database root file, including the path. Specify 'translog' to obtain the path and file name of the transaction log file, and 'translogmirror' to obtain the path and file name of the transaction log mirror file. If you do not specify the dbspace name or file ID, then the system dbspace is used. If the specified dbspace name or ID does not exist for the database to which you are connected, then the function returns NULL.

    • FileSize

      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, 'translog' to return the size of the transaction log file, and 'translogmirror' to return the size of the transaction log file mirror. If you do not specify the dbspace name or file ID, then the system dbspace is used. If the specified dbspace name or ID does not exist for the database to which you are connected, then the function returns NULL.

    • FreePages

      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 transaction log file. If you do not specify the dbspace name or file ID, then the system dbspace is used. If the specified dbspace name or ID does not exist for the database to which you are connected, then the function returns NULL.

    • IOParallelism

      Specify the name of a dbspace, or the file ID for the dbspace, to obtain the estimated number of simultaneous I/O operations supported by the dbspace. If you do not specify the dbspace name or file ID, then the system dbspace is used. If the specified dbspace name or ID does not exist for the database to which you are connected, then the function returns NULL.

    • MirrorServerState

      Specify a server name to determine the connection status of the mirror server. Returns connected, disconnected, incoming only, outgoing only, or NULL. The value is NULL when the database is not mirrored; Connected when there is a connection from this server to a specified server and a connection from the specified server to this server; Disconnected when there are no connections between this server and the specified server; Incoming only when there is a connection from the specified server to this server; and Outgoing only when there is a connection from this server to the specified server.

    • MirrorState

      Specify a server name to determine the synchronization status of the mirror server. Returns synchronizing, synchronized, or NULL. The value is Synchronizing when the mirror server is not connected, or has not yet read all the primary server's log pages, or if the synchronization mode is asynchronous. The value is Synchronized when the mirror server is connected and has all of the changes that have been committed on the primary server. If the database is not being mirrored the value is NULL.

    • NcharCharSet

      Specify the name of a standard to obtain the default NCHAR character set encoding label for that 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.

    • NextScheduleTime

      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

VARCHAR

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.

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;
Privileges

No privileges are required to execute this function for the current database. To execute this function for other databases, you must have either the SERVER OPERATOR or MONITOR system privilege.

NULL is returned if you specify an invalid parameter value or don't have one of the required system privileges.

Standards
  • ANSI/ISO SQL Standard

    Not in the standard.

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

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