Caché Transact-SQL (TSQL) Migration Guide
TSQL Functions
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Supported Functions
The following TSQL functions are implemented.
ABS
ABS(num)
Returns the absolute value of num. Thus both 123.99 and –123.99 return 123.99.
ACOS
ACOS(float)
Arc cosine: returns the angle in radians whose cosine is float. Thus 1 returns 0.
ASCII
ASCII(char)
Returns the integer value corresponding to the first character in string char. Thus, ASCII('A') returns 65.
ASCII is functionally identical to UNICODE. The reverse of this function is CHAR.
ASIN
ASIN(float)
Arc sine: returns the angle in radians whose sine is float. Thus 1 returns 1.570796326...
ATAN
ATAN(float)
Arc tangent: returns the angle in radians whose tangent is float. Thus 1 returns .785398163...
AVG
AVG(numfield)
AVG(DISTINCT numfield)
Aggregate function: used in a query to return the average of the values in the numfield column. For example, SELECT AVG(Age) FROM Sample.Person. AVG(DISTINCT numfield) averages the number of unique values in the field column. Fields with NULL are ignored.
CAST
CAST(expression AS datatype)
Returns the expression converted to the specified datatype. CAST can be used with any supported data type. For further details, refer to Data Types in the Caché SQL Reference.
When expression is a date value string, such as '2004–11–23' and datatype is TIMESTAMP or DATETIME, a time value of '00:00:00' is supplied.
When expression is a time value string, such as '1:35PM' and datatype is TIMESTAMP or DATETIME, the time is converted to a 24-hour clock, the AM or PM suffix is removed, a missing seconds interval is filled in with zeros, and the default date value of '1900–01–01' is supplied. Thus '1:35PM' is converted to '1900–01–01 13:35:00'.
When expression is a date value string, such as '2004–11–23' and datatype is DATE, the date is returned in Caché $HOROLOG date format, such as 60703 (March 14, 2007).
Caché TSQL does not support data type XML. However, instead of generating an error during compilation, CAST(x AS XML) in SQL mode generates CAST(x AS VARCHAR(32767)). In procedure mode, CAST(x AS XML) does not generate any conversion.
See CONVERT.
CEILING
CEILING(num)
Returns the closest integer greater than or equal to num. Thus 123.99 returns 124, –123.99 returns –123.
CHAR
CHAR(num)
Returns the character corresponding to the integer value num. Thus CHAR(65) returns A.
CHAR is functionally identical to NCHAR. The reverse of this function is ASCII.
CHAR_LENGTH / CHARACTER_LENGTH
CHAR_LENGTH(string)
CHARACTER_LENGTH(string)
Returns the number of characters in string.
CHARINDEX
CHARINDEX(seekstring,target[,startpoint])
Returns the position in target (counting from 1) corresponding to first character of the first occurrence of seekstring. You can use the optional startpoint integer to specify where to begin the search. The return value counts from the beginning of target, regardless of the startpoint. If startpoint is not specified, specified as 0, 1, or as a negative number, target is searched from the beginning. CHARINDEX returns 0 if seekstring is not found.
COALESCE
COALESCE(expression1,expression2,...)
Returns the first non-null expression from the specified list of expressions.
COL_NAME
COL_NAME(object_id,column_id)
Returns the name of the column. Can be used in procedure code or trigger code.
TSQL supports the two-argument form of this function. It does not support a third argument.
The following example returns the column name of the 4th column of Sample.Person:
  SET sql=2
  SET sql(1)="SELECT 'column name'=COL_NAME(id,4) FROM Sample.Person"
  SET sql(2)="WHERE id=OBJECT_ID('Sample.Person')"
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  SET result=statement.%Execute()
  DO result.%Display()
 
CONVERT
CONVERT(datatype,expression[,format-code])
Returns the expression converted to the specified datatype.
When datatype is BIT and expression is a boolean value: if the input value is a non-zero number, the result is 1. if the input value is 0, the result is 0. If the input value is the string 'TRUE' (case insensitive), the result is 1. If the input value is the string 'FALSE' (case insensitive), the result is 0. If the input value is NULL, the result is NULL. Any other input value generates an SQLCODE -141 error.
When datatype is datetime or timestamp and expression is a date value string, such as '2004–11–23', a time value of '00:00:00' is supplied. When expression is a time value string, such as '1:35PM' and datatype is datetime or timestamp, the time is converted to a 24-hour clock, the AM or PM suffix is removed, a missing seconds interval is filled in with zeros, and the default date value of '1900–01–01' is supplied. Thus '1:35PM' is converted to '1900–01–01 13:35:00'.
CONVERT supports the DATETIME2 data type. Caché maps DATETIME2 to system-defined DDL mapping %Library.TimeStamp. This mapping is supplied with new installs; if you are using an upgrade install, you may need to create this mapping.
The optional format-code argument is used to specify a date/time format when converting a datetime or timestamp value to a string. By specifying various format codes you can return a dates and times in a variety of different formats. The available format codes are 0 through 14 (100 through 114), 20 & 21 (120 & 121), 126, 130, and 131. The default format-code is 120:
yyyy-mm-dd hh:mm:ss
The 20 & 21 (120 & 121) format codes also support the format:
yyyy-mm-ddThh:mm:ss.fff
For further details, refer to the functionally identical Caché SQL CONVERT function in the Caché SQL Reference.
See CAST.
COS
COS(float)
Cosine: returns the cosine of the angle specified in float. Thus 1 returns .540302305...
COT
COT(float)
Cotangent: returns the cotangent of the angle specified in float. Thus 1 returns .64209261593...
COUNT
COUNT(field)
COUNT(DISTINCT field)
COUNT(*)
COUNT(1)
Aggregate function: used in a query to return the count of the values in the field column. Fields with NULL are not counted. For example, SELECT COUNT(Name) FROM Sample.Person. COUNT(*) and COUNT(1) are synonyms, they count all rows. COUNT(DISTINCT field) counts the number of unique values in the field column. Fields with NULL are not counted.
CURRENT_DATE
CURRENT_DATE
CURRENT DATE
Returns the current local date in the following format:
yyyy-mm-dd
The two syntax forms, with and without an underscore, are identical. Note that no parentheses are used with this function.
This function is provided for compatibility with SQL Anywhere; it is supported by both the Sybase and MSSQL dialects.
CURRENT_TIME
CURRENT_TIME
CURRENT TIME
Returns the current local time in the following format:
hh:mm:ss
Time is specified using a 24-hour clock, Fractional seconds are not returned.
The two syntax forms, with and without an underscore, are identical. Note that no parentheses are used with this function.
This function is provided for compatibility with SQL Anywhere; it is supported by both the Sybase and MSSQL dialects.
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP
CURRENT TIMESTAMP
Returns the current local date and time in the following format:
yyyy-mm-dd hh:mm:ss
Time is specified using a 24-hour clock, Fractional seconds are not returned.
The two syntax forms, with and without an underscore, are identical. Note that no parentheses are used with this function.
CURRENT_USER
CURRENT_USER
Returns the name of the current user.
Note that no parentheses are used with this function.
DATALENGTH
DATALENGTH(expression)
Returns an integer specifying the number of bytes used to represent expression. Thus 'fred' returns 4, and +007.500 returns 3.
DATEADD
DATEADD(code,num,date)
Returns the value of date modified by adding the interval specified in code the num number of times. The date can be a date, time, or date/time string in a variety of formats. You can specify any of the following code values, either the abbreviation (left column) or the name (right column):
yy Year
qq Quarter
mm Month
dy DayofYear
dd Day
dw, w Weekday
wk Week
hh Hour
mi Minute
ss Second
ms Millisecond
Code values are not case-sensitive. Day, DayofYear, and Weekday all return the same value.
The value returned by DATEADD always includes both date and time in the format:
yyyy-mm-dd hh:mm:ss.n
Fractional seconds are only returned if the source contained fractional seconds.
If a date is not specified (that is, if date contains only a time value), it defaults to 1/1/1900.
If a time is not specified in date, it defaults to 00:00:00. Hours are always returned based on a 24-hour clock.
DATEDIFF
DATEDIFF(code,startdate,enddate)
Returns the number of code intervals between startdate and enddate. The two dates can be a date, a time, or a date/time string. in the following format:
yyyy-mm-dd hh:mm:ss.n
You can specify any of the following code values, either the abbreviation (left column) or the name (right column):
yy Year
mm Month
dd Day
dw, w Weekday
wk Week
hh Hour
mi Minute
ss Second
ms Millisecond
Code values are not case-sensitive. Day, DayofYear, and Weekday all return the same value.
If a date is not specified (that is, if startdate or enddate contains only a time value), it defaults to 1/1/1900.
If a time is not specified in startdate or enddate, it defaults to 00:00:00.
DATENAME
DATENAME(code,date)
Returns the value of the part of the date specified by code as a string. The date can be a date, time, or date/time string in a variety of formats. date must be specified as a quoted string; code permits, but does not require enclosing quotes. Available code values are:
yyyy, yy
year
Year. Returns a four-digit year. If a two-digit year is specified, DATENAME supplies '19' as first two digits.
qq, q
quarter
Quarter. Returns an integer 1 through 4.
mm, m
month
Month. Returns the full name of the month. For example, 'December'.
dy, y
dayofyear
Day of Year. Returns an integer count of days 1 through 366.
dd, d
day
Day of Month. Returns an integer count 1 through 31.
wk, ww
week
Week of Year. Returns an integer count 1 through 53.
dw, w
weekday
Day of Week. Returns the number of the day of the week, counting from Sunday. For example, 3 is Tuesday.
hh
hour
Hour. Returns the hour of the day (24–hour clock), an integer 0 through 23.
mi, n
minute
Minute. Returns an integer 0 through 59.
ss, s
second
Second. Returns a decimal number 0 through 59 which may have a fractional part representing milliseconds.
ms
millisecond
Millisecond. Returns the fractional part of a second as an integer.
Code values are not case-sensitive.
If a date is not specified, it defaults to 1/1/1900. Two-digit years default to 19xx.
If a time is not specified, it defaults to 00:00:00. Hours are always returned based on a 24-hour clock. Seconds are always returned with fractional seconds, if fractional seconds are defined. Milliseconds are returned as an integer, not a decimal fraction.
DATEPART
DATEPART(code,date)
Returns the value of the part of the date specified in code as an integer. The date can be a date, time, or date/time string in a variety of formats. Available code values are listed in DATENAME.
DAY
DAY(date)
Returns the day portion of the specified date or date/time string. The date can be specified in ODBC timestamp format:
yyyy-mm-dd hh:mm:ss.n
The date must contain a date component. The date separator must be a hyphen (-).
The date can also be specified in Caché $HOROLOG date format, such as 60703 (March 14, 2007).
DB_NAME
DB_NAME()
Returns the current namespace name. No argument is permitted.
DEGREES
DEGREES(float)
Converts an angle measurement in radians to the corresponding measurement in degrees.
ERROR_MESSAGE
When invoked from within a CATCH block, returns the current error message. Otherwise, returns NULL.
ERROR_NUMBER
When invoked from within a CATCH block, returns the current SQLCODE error. Otherwise, returns NULL.
EXEC
EXEC(@var)
Executes dynamic SQL at runtime, as shown in the following example:
DECLARE @dyncode VARCHAR(200)
SELECT @dyncode='SELECT TOP 4 Name,Age FROM Sample.Person'
EXEC(@dyncode)
Compare this dynamic execution with the EXECUTE command that executes a stored procedure.
EXP
EXP(num)
Returns the exponential of num. This is the e constant (2.71828182) raised to the power of num. Thus EXP(2) returns 7.3890560989.
FLOOR
FLOOR(num)
Returns the closest integer less than or equal to num. Thus 123.99 returns 123, –123.99 returns –124.
GETDATE
GETDATE()
Returns the current local date and time in the following format:
yyyy-mm-dd hh:mm:ss.n
Time is specified using a 24-hour clock, Fractional seconds are returned.
GETUTCDATE
GETUTCDATE()
Returns the current UTC (Greenwich Mean Time) date and time in the following format:
yyyy-mm-dd hh:mm:ss.n
Time is specified using a 24-hour clock, Fractional seconds are returned.
HOST_NAME
HOST_NAME()
Returns the system name of the current host system.
INDEX_COL
INDEX_COL(table_name,index_id,key,[,user_id])
Returns the name of the indexed column in the specified table. table_name can be fully qualified. index_id is the number of the table's index. key is a key in the index, a value between 1 and sysindexes.keycnt (for a clustered index) or sysindexes.keycnt+1 (for a non-clustered index). user_id is parsed but ignored.
ISNULL
ISNULL(expr,default)
If expr is NULL, returns default. If expr is not NULL, returns expr.
ISNUMERIC
ISNUMERIC(expression)
A boolean function that returns 1 if expression is a valid numeric value; otherwise, returns 0.
LEFT
LEFT(string,int)
Returns int number of characters from string, counting from the left. If int is larger than string, the full string is returned. See RIGHT.
LEN
LEN(string)
Returns the number of characters in string.
LOG
LOG(num)
Returns the natural logarithm of num. Thus LOG(2) returns .69314718055.
LOG10
LOG10(num)
Returns the base-10 logarithm of num. Thus LOG10(2) returns .301029995663.
LOWER
LOWER(string)
Returns string with all uppercase letters converted to lowercase. See UPPER.
LTRIM
LTRIM(string)
Removes leading blanks from string. See RTRIM.
MAX
MAX(numfield)
Aggregate function: used in a query to return the largest (maximum) of the values in the numfield column. For example:
SELECT MAX(Age) FROM Sample.Person
Fields with NULL are ignored.
MIN
MIN(numfield)
Aggregate function: used in a query to return the smallest (minimum) of the values in the numfield column. For example:
SELECT MIN(Age) FROM Sample.Person
Fields with NULL are ignored.
MONTH
MONTH(date)
Returns the month portion of the specified date or date/time string. The date can be specified in ODBC timestamp format:
yyyy-mm-dd hh:mm:ss.n
The date separator must be a hyphen (-). Dates in any other format return 0.
The date can also be specified in Caché $HOROLOG date format, such as 60703 (March 14, 2007).
NCHAR
NCHAR(num)
Returns the character corresponding to the integer value num. Thus NCHAR(65) returns A.
NCHAR is functionally identical to CHAR. The reverse of this function is ASCII.
NEWID
NEWID()
Returns a unique value of a type compatible with the SQL Server UNIQUEIDENTIFIER data type. UNIQUEIDENTIFIER is a system-generated 16-byte binary string, also known as a GUID (Globally Unique ID). Caché TSQL does not support UNIQUEIDENTIFIER; it instead uses VARCHAR(32) as the data type for a Globally Unique ID.
NEWID() can be used to specify the DEFAULT value when defining a field.
NOW
NOW(*)
Returns the current local date and time in the following format:
yyyy-mm-dd hh:mm:ss
Time is specified using a 24-hour clock, Fractional seconds are not returned.
Note that the asterisk within the parentheses is required.
NULLIF
NULLIF(expr1,expr2)
Returns NULL if expr1 is equivalent to expr2. Otherwise, returns expr1.
OBJECT_ID
OBJECT_ID(objname,objtype)
Takes the object name as a quoted string, and optionally the object type, and returns the corresponding object ID of the specified object as an integer. The available objtype values are as follows: RI = FOREIGN KEY constraint; K = PRIMARY KEY or UNIQUE constraint; P = Stored procedure; S = System table; TR = Trigger; U = User table; V = View.
CREATE PROCEDURE GetName
AS SELECT OBJECT_ID('Person','U')
GO
Returns the NULL if objname does not exist, or if the optional objtype is specified and does not match the objname. Can be used within procedure code or trigger code. The inverse of OBJECT_NAME.
OBJECT_NAME
OBJECT_NAME(id)
Takes the object ID integer and returns the corresponding object name of the specified object. Returns the empty string if id does not exist. Can be used within procedure code or trigger code. The inverse of OBJECT_ID.
CREATE PROCEDURE GetID
AS SELECT OBJECT_NAME(22)
GO
PATINDEX
PATINDEX(pattern,string)
Returns an integer specifying the beginning position of the first occurrence of pattern in string, counting from 1. If pattern is not found in string, 0 is returned. Specify pattern as a quoted string. Comparisons are case sensitive. The pattern can contain the following wildcard characters:
% Zero or more characters. For example, '%a%' returns the position of the first occurrence of 'a' in string, including 'a' as the first character in string.
_ Any single character. For example, '_l%' returns 1 if string begins with a substring such as 'Al', 'el', and 'il'.
[xyz] Any single character from the specified list of characters. For example, '[ai]l%' returns 1 if string begins with the substring 'al' or 'il', but not 'el' or 'Al'.
[a-z] Any single character from the specified range of characters. For example, '%s[a-z]t%' matches 'sat', 'set', and 'sit'. A range must be specified in ascending ASCII sequence.
The caret (^) character is a not a wildcard character; if included within square brackets it is treated as a literal. A pattern commonly consists of a search string enclosed in percent (%) characters '%Chicago%' indicating that the entire string should be searched.
PI
PI()
Returns the constant pi. The parentheses are required; no argument is permitted. Thus PI() returns 3.141592653589793238.
POWER
POWER(num,exponent)
Returns the value num raised to exponent.
QUOTENAME
QUOTENAME(value)
Returns value as a delimited identifier. TSQL supports double quotes ("value") as delimiter characters. For example:
PRINT 123
  // returns 123
PRINT QUOTENAME(123)
  // returns "123"
RADIANS
RADIANS(float)
Converts an angle measurement in degrees to the corresponding measurement in radians.
RAND
RAND([seed])
Returns a random number as a fractional number less than 1. The optional seed integer argument is ignored; it is provided for compatibility. If RAND is used more than once in a query it returns different random values.
REPLACE
REPLACE(target,search,replace)
Finds every instance of the search string in the target string and replaces it with the replace string, and returns the resulting string. To remove the search string from the target string, specify replace as an empty string.
REPLICATE
REPLICATE(expression,repeat-count)
REPLICATE returns a string of repeat-count instances of expression, concatenated together.
If expression is NULL, REPLICATE returns NULL. If expression is the empty string, REPLICATE returns an empty string.
If repeat-count is a fractional number, only the integer part is used. If repeat-count is 0, REPEAT returns an empty string. If repeat-count is a negative number, NULL, or a non-numeric string, REPEAT returns NULL.
Repeats string for the number of times specified by the integer num.
REPLICATE returns NULL if either argument is NULL. If string is the empty string (''), REPLICATE returns the empty string. If num is a negative number, REPLICATE returns NULL. If num is zero, REPLICATE returns the empty string.
REVERSE
REVERSE(string)
Reverses the order of the characters in string.
RIGHT
RIGHT(string,int)
Returns int number of characters from string, counting from the right. If int is larger than string, the full string is returned. See LEFT.
ROUND
ROUND(num,length)
Returns num rounded to the number of decimal digits specified by the integer length. If length is greater than the number of decimal digits, no rounding is performed. If length is 0, num is rounded to an integer. If the length argument is omitted, it defaults to 0. If length is a negative integer, num is rounded to the left of the decimal point. A third argument is not accepted by ROUND.
RTRIM
RTRIM(string)
Removes trailing blanks from string.
SCOPE_IDENTITY
Returns the last identity value inserted into an IDENTITY column in the same scope. However, the last IDENTITY is not limited to the scope of the current procedure. Therefore, you should only use SCOPE_IDENTITY when you know that a statement within the current procedure has generated an IDENTITY value. For example, SCOPE_IDENTITY should be used after an INSERT command in the same procedure.
The following Dynamic SQL example returns the IDENTITY value from the second INSERT:
  SET sql=6
  SET sql(1)="CREATE TABLE #mytest (MyId INT IDENTITY(1,1),"
  SET sql(2)="Name VARCHAR(20))"
  SET sql(3)="INSERT INTO #mytest(Name) VALUES ('John Smith')"
  SET sql(4)="INSERT INTO #mytest(Name) VALUES ('Walter Jones')"
  SET sql(5)="PRINT SCOPE_IDENTITY()"
  SET sql(6)="DROP TABLE #mytest"
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  SET result=statement.%Execute()
  DO result.%Display()
 
SIGN
SIGN(num)
Returns a value indicating the sign of num. If num is negative (for example, -32), it returns -1. If num is positive (for example, 32 or +32), it returns 1. If num is zero (for example, 0 or -0), it returns 0.
SIN
SIN(float)
Sine: returns the sine of the angle specified in float. Thus 1 returns .841470984807...
SPACE
SPACE(num)
Returns a string of blank spaces of length num.
SQRT
SQRT(num)
Returns the square root of num. Thus SQRT(9) returns 3.
SQUARE
SQUARE(num)
Returns the square of num. Thus SQUARE(9) returns 81.
STR
STR(num,[length[,precision]])
Returns a string of length characters. If the integer length is equal to or greater than the number of characters in the numeric num (including decimal point and sign characters), STR returns num converted to a string and padded with leading blanks to make the resulting string of length characters.
If the optional integer precision is specified, num is truncated to the specified number of decimal digits before string conversion. If precision is omitted, num is truncated to its integer portion. If precision is larger than the number of decimal digits, num is padded with trailing zeros before string conversion.
If length is omitted, it defaults to 10. If length is less than the number of characters in num (after adjustment by precision) a dummy string consisting of all asterisks of length number of characters is returned.
STUFF
STUFF(string,start,length,replace)
Returns string with length number of characters removed and the replace string inserted. The point of removal and insertion is specified by the start integer, counting from the beginning of string. If length is 0, no characters are removed. If replace is the empty string, no characters are inserted.
If start is greater than the number of characters in string, no value is returned. If start is 1, length number of characters are removed from the beginning of string and the replace string inserted. If start is 0, length minus 1 number of characters are removed from the beginning of string and the replace string inserted.
If length is greater than or equal to the number of characters in string, the replace string is returned. The replace string length is not limited by the length of string or length.
SUBSTRING
SUBSTRING(string,start,length)
Returns a substring of string beginning at the location start for the length number of characters. If start is greater than the length of string, or if length is 0, no string is returned.
SUM
SUM(numfield)
SUM(DISTINCT numfield)
Aggregate function: used in a query to return the sum of the values in the numfield column. For example:
SELECT SUM(Age) FROM Sample.Person
SUM(DISTINCT numfield) sums the unique values in the field column. Fields with NULL are ignored.
SUSER_NAME
SUSER_NAME()
Returns the name of the current OS user. Parentheses are required, no argument is permitted. Equivalent to TSQL USER_NAME(), the Caché SQL USER function, and the Caché ObjectScript $USERNAME special variable.
SUSER_SNAME
SUSER_SNAME()
Returns the name of the current OS user. Parentheses are required, no argument is permitted. Equivalent to TSQL USER_NAME(), the Caché SQL USER function, and the Caché ObjectScript $USERNAME special variable.
TAN
TAN(float)
Tangent: returns the tangent of the angle specified in float. Thus 1 returns 1.55740772465...
TEXTPTR
TEXTPTR(field)
Returns an internal pointer to the image or text column data specified in field. The data type of this pointer is VARBINARY(16).
TEXTVALID
TEXTVALID('table.field',textpointer)
Takes an internal pointer to an image or text column from TEXTPTR, and compares it to a specified in table.field. Returns 1 if the pointer points to the specified table.field. Otherwise, returns 0.
UNICODE
UNICODE(char)
Returns the Unicode integer value corresponding to the first character in the string char. Thus, UNICODE('A') returns 65.
UNICODE is functionally identical to ASCII. The reverse of this function is CHAR.
UPPER
UPPER(string)
Returns string with all lowercase letters converted to uppercase. See LOWER.
USER
USER
Returns the name of the current user.
Note that no parentheses are used with this function.
USER_NAME
USER_NAME([userid])
Returns the name of the user specified by user ID. If the optional userid is omitted, returns the name of the current user. The argument is optional; the parentheses are mandatory.
YEAR
YEAR(date)
Returns the year portion of the specified date or date/time string. The date can be specified in ODBC timestamp format:
yyyy-mm-dd hh:mm:ss.n
The date separator can be either a hyphen (-) or a slash (/).
The date can also be specified in Caché $HOROLOG date format, such as 60703 (March 14, 2007).
Unsupported Functions
The following Microsoft Transact-SQL functions are not supported by TSQL at this time: APP_NAME, ATN2, BINARY_CHECKSUM, CHECKSUM, COL_LENGTH, COLLATIONPROPERTY, COLUMNPROPERTY, CURSOR_STATUS, DATABASEPROPERTY, DATABASEPROPERTYEX, DB_ID, DIFFERENCE, FILE_ID, FILE_NAME, FILEGROUP_ID, FILEGROUP_NAME, FILEGROUPPROPERTY, FILEPROPERTY, FORMATMESSAGE, FULLTEXTCATALOGPROPERTY, FULLTEXTSERVICEPROPERTY, GETANSINULL, HOST_ID, IDENT_CURRENT, IDENT_INCR, IDENT_SEED, IDENTITY, INDEXKEY_PROPERTY, INDEXPROPERTY, ISDATE, IS_MEMBER, IS_SRVROLEMEMBER, OBJECTPROPERTY, PARSENAME, PERMISSIONS, ROWCOUNT_BIG, SERVERPROPERTY, SESSIONPROPERTY, SESSION_USER, SOUNDEX, SQL_VARIANT_PROPERTY, STATS_DATE, STDEV, STDEVP, SYSTEM_USER, TYPEPROPERTY.