Global variables represent a special type of variable. You do not need to declare them, since the server constantly maintains them. Global Variable names begin with a @@prefix. Users can not create Global variables and can not update the value of global variables directly in a select statement. The server always maintain the values of these variables.
Global variables were system-supplied, predefined variables that were distinguished from local variables by having two at symbols (@@) preceding their names.
All the global variables represent information specific to the server or a current session.
Global Variables with their scope :
Global variables were system-supplied, predefined variables that were distinguished from local variables by having two at symbols (@@) preceding their names.
All the global variables represent information specific to the server or a current session.
Global Variables with their scope :
1. @@CONNECTIONS
Return : The total number of connections since SQL Server started
Scope : Server
2.@@CPU_BUSY
Return : The Total amount of CPU time, in milliseconds since SQL Server started
Scope : Server
3.@@CURSOR_ROWS
Return : The number of rows returned by the last cursor to be opened
Scope : Connection
4.@@DATEFIRST
Return : The day of week currently set as first day of week. 1 represent Monday, 2 as tuesday and so on , for example : if sunday is the first day of week @@DATEFIRST returns 7
Scope : Connection
5.@@IDENTITY
Return : The last identity value generated for the current connection
Scope : Connection
6.@@DBTS
Return : Current Database wide time stamp value
Scope : Database
7.@@Error
Return : The error value for the last T-SQL statement executed
Scope : Connection
8.@@FETCH_STATUS
Return : The row status from the last curser fetch command
Scope : Connection
9.@@IDLE
Return : The total number of milliseconds has been idle since it was started
Scopr : Server
10.@@IO_BUSY
Return : The total number of milliseconds SQL Server has been performing disk operations since it was started
Scope : Server
11.@@LANGID
Return : The language ID used by the current connection
Scope : Connection
12.@@LANGUAGE
Return : The language , by name used by the current connection
Scope : Connection
13.@@Lock_Timeout
Return : The lock timeout setting for current connection
Scope : Connection
14.@@Max_Connection
Return : The current maximum number of concurrent connection for SQL Server
Scope : Server
15. @@MAX_PRECISION
Return : The decimal and Numeric Maximum precision setting
Scope : Server
16.@@Options
Return : A binary representation of all the current connection options
Scope : Connection
17.@@Nestlevel
Return : The current number of nested stored procedure
Scope : Connection
18.@@Pack_Received
Return : The total number of network communication packets received by the sql server since it was started
Scope : Server
19.@@Version
Return : The sql server version, edition and service pack
Scope : Server
20.@@TRANCOUNT
Return : The number of Active Transactions for the current connection
Scope : Connection
21.@@TOTAL_READ
Return : The total number of disk reads by the sql server since it was started
Scope : Server
22.@@TOTAL_WRITE
Return : The total number of disk writes by the sql server since it was started
Scope : Server
23.@@Pack_Sent
Return : The total number of network-communication packets sent by the sql server since it was started
Scope : Server
24.@@Packet_Errors
Return : The total number of network communication packets recognized by the sql server since it was started
Scope : Server
25.@@PROCID
Return : The store procedure identifier for the current store procedure. This can be used with SYSOBJECTS to determine the name of current store procedure, as example
Select NAME From SYSOBJECTS where id ==@@PROCID
Scope : Connection
26.@@REMSERVER
Return : The name of the Login server when the running remote stored procedures
Scope : Connection
27.@@ROW_COUNT
Return : The number of rows returned by the LAST T-SQL Statement
Scope : Connection
28.@@SERVERNAME
Return : The name of the current server
Scope : Server
29.@@SERVICENAME
Return : The sql server's windows service name
Scope : Server
30.@@SPID
Return : The current connection's server process identifier - the id for the connection
Scope : Connection
31.@@TEXTSIZE
Return : The current Maximum size of BLOB data (Text, nText or image )
Scope : Connection
32.@@Timeticks
Return : The number of milliseconds per tick
Scope : Server
33.@@TOTAL_ERRORS
Return : The total number of disk errors committed by the sql server since it was started
Scope : Server
Many of the global variables can be found by the examining the configuration functions , system functions and system statistical functions folder of the object browser, which is the part of the query analyzer.
Return : The decimal and Numeric Maximum precision setting
Scope : Server
16.@@Options
Return : A binary representation of all the current connection options
Scope : Connection
17.@@Nestlevel
Return : The current number of nested stored procedure
Scope : Connection
18.@@Pack_Received
Return : The total number of network communication packets received by the sql server since it was started
Scope : Server
19.@@Version
Return : The sql server version, edition and service pack
Scope : Server
20.@@TRANCOUNT
Return : The number of Active Transactions for the current connection
Scope : Connection
21.@@TOTAL_READ
Return : The total number of disk reads by the sql server since it was started
Scope : Server
22.@@TOTAL_WRITE
Return : The total number of disk writes by the sql server since it was started
Scope : Server
23.@@Pack_Sent
Return : The total number of network-communication packets sent by the sql server since it was started
Scope : Server
24.@@Packet_Errors
Return : The total number of network communication packets recognized by the sql server since it was started
Scope : Server
25.@@PROCID
Return : The store procedure identifier for the current store procedure. This can be used with SYSOBJECTS to determine the name of current store procedure, as example
Select NAME From SYSOBJECTS where id ==@@PROCID
Scope : Connection
26.@@REMSERVER
Return : The name of the Login server when the running remote stored procedures
Scope : Connection
27.@@ROW_COUNT
Return : The number of rows returned by the LAST T-SQL Statement
Scope : Connection
28.@@SERVERNAME
Return : The name of the current server
Scope : Server
29.@@SERVICENAME
Return : The sql server's windows service name
Scope : Server
30.@@SPID
Return : The current connection's server process identifier - the id for the connection
Scope : Connection
31.@@TEXTSIZE
Return : The current Maximum size of BLOB data (Text, nText or image )
Scope : Connection
32.@@Timeticks
Return : The number of milliseconds per tick
Scope : Server
33.@@TOTAL_ERRORS
Return : The total number of disk errors committed by the sql server since it was started
Scope : Server
Many of the global variables can be found by the examining the configuration functions , system functions and system statistical functions folder of the object browser, which is the part of the query analyzer.
No comments:
Post a Comment