SQL Server Common built-in functions (built-in) Keep organizing

This article is used to collect system built-in functions that are frequently used in operations, and to continuously organize

One, commonly used metadata function

1. View the database ID and name

db_id (' db Name '),db_name(' db ID ')

2. View object ID and name, object schema, object definition

 object_id  ( " schema_name. object_name  , "  object_type   " )  object_name  (object_id  [ , database_id   ) Object_schema_name (  object_id  [ , database_id   )  object_definition ( object_id ) 

3, look at the schema ID and name, get the schema name of the object by Object ID (Schema)

[]  )  []object_id [ ] )  

4, view the name of column

col_name (table_id,column_id)

Second, Common Security Function

1, the name of the current database user

Current_User

2. View the name and ID of the database user

user_name []  )user_id[ ]  Principal_name' )  

3. View the name and ID of the server user (server user, login)

Login is the server principal (server Principal), has a name and id attribute, and each login has a security identity (SID).

--return login ID, parameter is login Namesuser_id([' Login '] )            --return to login Name, parameter is login ID,Suser_name([Login_id])--return sid (Security identification), parameter is login NameSuser_sid([' Login '] [, Param2] )    --return login Name, parameter is SIDsuser_sname([SID])

The difference between 4,sid and ID

When creating a SQL Server login, from the sys.server_principals system view, you can see that the login is assigned an ID and SID, in the case of SQL Server, the ID and SID are unique, except that ID identifies login as a securable and Sid identifies the security Context of login. In general, IDs can be reused, but SIDS are generally not reusable. When the same login is created repeatedly, its ID may change, but its SID will not change.

Not only login has ID and sid,database user also has. When a database user is created, from Sys.database_principals, you will be able to see that users are assigned an ID and SID. At the database level, the ID is unique. If user was created from SQL Server login, then the SID for user and login is the same. For more information, please refer to "SIDs and IDs"

Third, common global variables

Name,servicename and versions of 1,sql server

@ @SERVERNAME,@ @SERVICENAME,@ @VERSION

2, returns the Id,module of the current module including: Sp,udf,trigger

@ @PROCID -- Gets the current module Name Declare @ObjectName sysname; Select @ObjectName = object_name (@ @ProcID)

3, returns the ID of the current session, the current RequestID

@ @SPID

4, in the current session, returns the number of data rows affected by the previous query

@ @ROWCOUNT

5, current connection, returns the number of transactions that are open but not closed, view the ID of the current transaction, and the status of the transaction (1,0,-1)

The Xact_state () function returns the state of the transaction, and 1 indicates that there is an active transaction,0 indicating that no active transaction,-1 represents an active Transaction, but an error occurred that caused the transaction to not be committed.

@ @TRANCOUNT

6. Check the name (machine name and ID) of the current device (Host)

host_name (),host_id()

Four, Compress data and decompress data using gzip algorithm

COMPRESS (expression) decompress (expression)

When inserting data, compress the data, and the data type after compression is varbinary (max)

INSERT  into player (name, surname, info)   VALUES (n'ovidiu', n'cracium', COMPRESS (n'  {"Sport": "Tennis", "age": +, "rank": 1, "points": 15258, turn ": +}'

Extract data from varbinary (max) to original type when querying data

SELECTCASTasNVARCHAR(MAX as info    from

Five, Debug function

In tsql, use the try and catch to write exception handling code, in the Catch clause, using the Debug function, to get exception information

 --  Returns the line number of the code where the error occurred (linenumber)  error_line ()  --  return error number (errornumber)  error_number ()  @@ Error  --  Returns the fault message (errormessage)  Span style= "color: #000000;" >error_message ()  --  returns the SP Name where the error occurred  error_procedure ()  --  Returns the severity of the error (Error Severity)  error_severity ()  --  Returns the status of the error (Error state)  Error_state () 

When debugging, you can use the following sample script code to record exception information in a data table for troubleshooting code

--SET Xact_abort on would render the transaction uncommittable when the constraint violation occurs.SETXact_abort on; BEGINTRYBEGIN TRANSACTION; --A FOREIGN KEY constraint exists on this table. This statement would generate a constraint violation error.        DELETE  fromproduction.productWHEREProductID= 980; --If The delete operation succeeds, commit the transaction. The CATCH block would not execute.    COMMIT TRANSACTION; ENDTRYBEGINCATCH--Test xact_state for 0, 1, or-1.     --If 1, the transaction is committable.     --If-1, the transaction is uncommittable and should was rolled back.    --xact_state = 0 means there is no transaction and a commits or rollback operation would generate an error.      --Test Whether the transaction is uncommittable.    IF(Xact_state ())= -1      BEGIN          --Logging Exception Info, as the transaction is in a uncommittable state. Rolling back transaction.        SELECTerror_number () aserrornumber, error_severity () aserrorseverity, error_state () aserrorstate, Error_procedure () aserrorprocedure, Error_line () asErrorLine, Error_message () aserrormessage; ROLLBACK TRANSACTION; END; --Test Whether the transaction is active and valid.     IF(Xact_state ())= 1      BEGIN          --' The transaction is committable.  Committing transaction. '         COMMIT TRANSACTION; END; ENDCATCH; GO 

View Code

Reference Documentation:

Security Functions (Transact-SQL)

Metadata Functions (Transact-SQL)

Configuration Functions (Transact-SQL)

System Functions (Transact-SQL)

SQL Server Common built-in functions (built-in) Keep organizing