system functions for functions used in T-SQL queries

Function
Functions used in queries

In a query statement, you can query not only the values of the columns in the table but also the different types of information in the database using internal functions. You can use an intrinsic function in the select list, where clause, and any expression that allows it. These functions are all an extension of T-SQL to the SQL. The internal functions provided by Adaptive server can be grouped into the following categories:

L system functions. Returns the information in the database;

L String function. Used to process a string or string expression;

L-Text function. Data for processing text and image types;

L Mathematical type. Functions for dealing with triangles, geometries, and other data processing;

L Date function. Processing datetime and smalldatetime types of data;

L Data type conversion function (convert). Used to convert one data type to another, or to format dates into various forms.

L Security functions. Returns information on security services and user-defined roles

First, the system function

System functions return specific information from the database, and most of them provide an easy way to query system tables.

General syntax for system functions:

Select Function_name (Argument[s])

A system function can be used in a selection, in a WHERE clause, and in any place where an expression is allowed. For example, if you want to query the user ID for "ZYF", enter:

Select "User ID" =user_id ("ZYF")





Results:

User identifier number

——

3

In general, what information is returned in the function name representation.

USER_NAME returns the user's name using the ID number as the parameter. If, for example, a user name with a query ID number of 3

Select "Username" = user_name (3)

Results:

User name

——

Zyf

Find the name of the current user (that is, the user name being used)

Select USER_NAME ()

Results:

User name

——

Dbo

Adaptive Server processes user identifiers according to the following rules:

L When the system administrator becomes the owner of the database he uses, the login user ID is specified as 1;

L Guest user's login ID always-1

L The user of the database owner is always dbo, and his user ID is 1;

L The Guest user ID is 2



system function table

The name of the function

Parameters

return results

Col_name

(object_id,column_id[,database_id])

Column Name

Col_length

(Object_name,column_name)

The definition length of the column (using Datalength is to view the length of the actual data)

Curunreservedpgs

(Dbid,lstart,unreservedpgs)

The number of free pages in the disk slice. If the database is open, the value is from memory, and if the database is not open, the value is from the sysusages column in the system table Unreservedpgs

Data_pgs

(Object_id,{doampg | ioampg})

Number of pages used by the table (DOAMPG) or index (IOAMPG). The results do not include the number of pages used for internal results

Datalength

(expression)

Returns the length of the expression. Expression can be either a column name or a string constant. If it is a column name, returns the length of the actual data in the column.

db_id

([database_name])

The ID number of the database. The database name must be a character expression, and if the database name is a constants quantity, you must enclose it in quotation marks. If no database name is given, the ID number of the current database is returned

Db_name

([database_id])

The name of the database. DATABASE_ID must be a numeric expression that returns the current database name if database_id is not given.

host_id

()

Current host name of the client process (non-adaptive server process)

Host_name

()

The index column name. Returns null if object_name is not a table name or a view name

Index_col

(object_name,index_id,key_#[,user_id])

When expression1 is null, the value of the expression2 is substituted for the expression1 value. The value of an expression must be implicitly convertible, otherwise you must use the CONVERT function

Isnull

(Expression1,expression2)

The last machine that manages the log segment? Value. Lastchance: Creates a last chance value in the specified database, returns 1 when the last chance value for the specified database is exceeded, or returns 0;unsuspend: Used to wake a pending task in the database and use the opportunity value when the last chance value is exceeded Reserve: Returns the number of free log pages required to successfully dump a transaction log of a specified size.

Lct_admin

({{"Lastchance" | " Logfull "|" Unsuspend "}

, database_id}|

"Reserve"

, log_pages})

Number of pages assigned to a table or index

Reserved_pgs

(Object_id,{doampg|ioampg})

Estimated number of rows in a table

Rowent

(Doampg)

Database object ID Number

object_id

("objname")

Database object Name

object_name

(obj_id)

Returns the ID number used by the server from the table syslogins, and returns the server ID number of the current user if no value is given for the parameter server_user_name

suser_id

([Server_user_name])

Returns the server user name, the server user ID number is stored in the table syslogins, and if no value is given for the parameter server_user_id, the current user name is returned.

Tsequal

(TIMESTAMP,TIMESTAMP2)

In browse mode, compare timestamp values to prevent the submission of values that have been modified. The timestamp is the time flag of the row being browsed; TIMESTAMP2 is the time flag for the store row. This function allows the user to use Browse mode without invoking db_library.

Used_pgs

(Object_id,doampg,ioampg)

Number of columns used by tables and clustered indexes

User



User name

user_id

([user_name])

The ID number of the user. Reports the user ID number in the current database, and returns the ID number of the current user if no parameter user_name is given.

User_name

([user_id])

Returns the user name corresponding to the user ID number of the current database. If no value is supplied for the parameter user_id, the current user name is returned.

Valid_name

(character_expression)

Returns 0 if the character_expression is an invalid identifier (illegal character or length exceeds 30), otherwise it returns a value other than 0

Valid_user

(server_user_id)

Returns 1 if the specified ID number has its corresponding user name or alias in at least one database in the adaptive server, and only users of the Sa_role or sso_role roles can use the function.



Example:

1. Find the length of Title column in table titles

Slelect "title Length" =col_length ("article", "title")

Results:

Title length

-----

120

2. Find the length of the data in the Title Column of table article

Select Length=datalength (title), title

From article

Results:

Length title

24 soft disclosure of its Internet policy

Introduction of two new network software

IBM releases new SystemView

3, find all the writer's salary, if the salary is null value is replaced with $10.00

Select IsNull (salary,$10.00)

From Auths

Results:

——

120.00

100.00

110.00

10.00

4, find out the system table sysusers user ID is 1 user name

Select Name Form sysusers

where Name=user_name (1)

Results:

Name

——

Dbo