SQL Server function Full solution < five > system functions

System information includes the currently used database name, hostname, system error message, and user name, among other things. Use the system functions in SQL Server to obtain this information when needed. The function and usage of system functions are described below.

1. Returns the length of the specified field in the table

the Col_length (table,column) function returns the length value of the specified field in the table. The return value is the type int, and table is the name of the table for which to determine the column length information, which is an expression of type nvarchar. Columns is the name of the column whose length you want to determine, and is an expression of type nvarchar.
"Example" first creates a student table student.
CREATE TABLE Student (
I_SID int primary key identity (.),
Str_stuname varchar (10)
);
SELECT col_length (' Student ', ' str_stuname ');

2. Returns the name of the specified field in the table 

The Col_name (table_id,column_id) function returns the name of the specified field in the table. TABLE_ID is the identification number of the table, column_id is the identification number of the column, and the type is int.
"Example" Select Col_name (object_id (' Student '), 1);

3. The actual length function of the data that returns the data expression

　　The datalength (expression) function returns the actual length, or number of bytes, of data in the data expression. Its return value type is int. The length of NULL is NULL. Expression can be a type of renhe data.

"Example" Select Datalength (str_stuname) from student WHERE i_sid=1;

4. Return the number of the database 

The DB_ID (database_name) function returns the number of the database, whose return value is smallint type, and returns the number of the current database if no database_name is specified.
"Example" Select db_id (' Master '), db_id (' test_db ');

5. Return the name of the database 

The db_name (database_id) function returns the name of the database. The return value type is nvarchar (+). database_id is a smallint type of data. If database_id is not specified, the name of the current database is returned.
"Example" use master;
SELECT db_name (' master '), Db_name (db_id (' sample_db '));
The USE statement selects Master as the current database, so the db_name () return value for the current database Master,db_name (db_id (' sample_db ')) returns the value sample_db itself.

6. Returns the current default null value for the database

The Getansinull () (database_name) function returns the default null value for the current database with the return value type int. The Getansinull () function returns 1 for ANSI null values, or 0 if no ANSI null value is defined;
"Example" Select Getansinull (' master ');
If the specified database is nullability, that is, null values are allowed, and no display definition column or data type is nullability, Getansinull returns 1;

7. Return the identification number of the server-side computer

 The host_id () function returns the identification number of the server-side computer. Its return value type is char (10).

"Example" to view the identification number of the current server-side computer, as follows:
SELECT host_id ();
Use the host_id () function to record computer terminal IDs that insert data into the database.

8. Return the server-side computer name

The HOST_NAME () function returns the name of the server-side computer whose return value type is nvarchar (128);
"Example" to view the name of the current server-side computer, as follows:
SELECT HOST_NAME ();

9. Returns the number of the database object 

The object_id (database_name.schema_name.object_name,object_type) function returns the number of the database object whose return value type is int. Object_name is the object to be used, and its data type is varchar or nvarchar. If the object_name data type is varchar, it converts the hermit to nvarchar. You can choose whether to specify a database and schema name. OBJECT_TYPE Specifies the schema-scoped object type.
"Example" returns the object ID of the student table in the SAMPLE_DB database
SELECT object_id (' sample_db.dbo.student ');

10. Return the user's SID (Security identification number)

The SUSER_SID (login_name) function returns the user's SID (Security identification number, secure ID) based on the user's login name. Its return value type is int, and if login_name is not specified, the current user's SID is returned.
"Example" to view the security identification number of the current user, enter the following statement
SELECT Suer_sid ();

11. Return the user's login name

The SUSER_SNAME ([server_user_id]) function returns the login associated with the security identification number (SID) and, if no server_user_id is specified, returns the login name of the current user. Its return value type is nvarchar (128);
"Example" returns the login associated with the Windows Security identification number, as follows:
SELECT SUSER_SNAME (0x01), SUSER_SNAME ();

12. Return the name of the database object

The object_name (object_id[,database_id]) function returns the name of the database object. database_id the ID of the database in which to find the object, and the data type is int. OBJECT_ID is the object ID to be used, the data type is int, the object is assumed to be the specified database, and if database_id is not specified, it is assumed to be a schema-scoped object in the current database context, with a return value type of sysname;
"Example" View object name with Object ID value 325576198 in sample_db database
SELECT object_name (, db_id (' sample_db ')), object_id (' sample_db.dbo.student ');

13. Return the database user's identification number

The user_id (user) function returns the ID of the database user based on the username. The return value is of type int, and if no user is specified, the database ID of the current user is returned.
"Example" displays the current user's database identification number, enter the following statement
Use sample_db;
SELECT user_id ();

14. Return the database user name

The user_name (ID) function returns the database user name based on the ID number associated with the database user. The return value type is nvarchar (256). If no ID is specified, the user name of the current database is returned.
"Example" to find the current database name, enter the following statement
Use sample_db;
SELECT user_name ();

The SQL script for the example above

--system FunctionsCreate Databasesample_db; Usesample_db;Create TableStudent (I_sidint  Primary Key  Identity(1,1), Str_stunamevarchar(Ten)    );--1. Returns the length of the specified field in the tableSELECT col_length('Student','Str_stuname');--2. Returns the name of the specified field in the tableSELECT col_name(object_id('Student'),1);--3. The actual length function of the data that returns the data expressionInsert  intoStudentValues('Jamse');Select * fromStudentSELECT datalength(Str_stuname) fromStudentwhereI_sid=1;--4. Return the number of the databaseSELECT db_id('Master') ,db_id('sample_db');--5. Return the name of the database UseMasterSELECT db_name(),db_name(db_id('sample_db'));--6. Returns the current default null value for the databaseSELECT Getansinull('Master');--7. Return the identification number of the server-side computerSELECT host_id()--8. Return the server-side computer nameSELECT host_name();--9. Returns the number of the database objectSELECT object_id('sample_db.dbo.student');--10. Return the user's SID (Security identification number)SELECT Suser_sid('SA'),Suser_sid();--11. Return the user's login nameSELECT suser_sname(0x01),suser_sname();--12. Return the name of the database objectSELECT object_name(325576198,db_id('sample_db')),object_id('sample_db.dbo.student');--13. Return the database user's identification number Usesample_db;SELECT user_id();--14. Return the database user name Usesample_db;SELECT user_name();

SQL Server function Full solution < five > system functions