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

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Functions » Alphabetical list of functions

DAYS function [Date and time] Next Page

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-level properties.

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

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.

Remarks

Returns a value of type LONG VARCHAR. The current database is used if the second argument is omitted.

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;
See also
Standards and compatibility
Example

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 statement SELECT DB_EXTENDED_PROPERTY ( 'Collation', 'Properties' ); returns the tailoring options specified for the database CHAR collation:

'CaseSensitivity=Ignore'

The statement SELECT DB_EXTENDED_PROPERTY( 'NCharCollation', 'Specification' ); returns the full collation specification for the database NCHAR collation:

'UCA(CaseSensitivity=Ignore;AccentSensitivity=Ignore;PunctuationSensitivity=Primary)'