Hi all this post explores some of important variables and stored procedures built inside the SQL Server.
1) @@VERSION
We can use the @@VERSION system variable to determine product name, level, version, processor architecture(32 bit or 64 bit),
build date, and OS for the current installation of SQL Server.
Eg: Select @@VERSON
returns -
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2009 15:48:46 Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: )
here,
First line gives the SQL Server version name and the realease type
here Microsoft SQL Server 2008 R2 is the product name
RTM gives the product level
10.50.1600.1 here indicates the product version.
Second line gives the installation date.
Third line indicates the Processor architecture and edition of the SQL Server.
here Enterprise Edition gives the product edition
2)sp_spaceused
The stored procdedure sp_spacedused gives the space used, no of records, index size etc used by a particular table .
Eg: sp_spaceused tbl_Emp_master
returns -
name rows reserved data index_size unused
tbl_Emp_master 63732 68640 KB 61600 KB 6088 KB 952 KB
the above sp can be used to find the details about a database also by just using the sp name that is sp_spaceused and executing.
3)@@SERVERNAME
The @@SERVERNAME variable returns the machine name on which SQL Server is running.
4) @@MAX_CONNECTIONS
The @@MAX_CONNECTIONS variable returns the maximum number of connections allowed simultaneously to the SQL Server.
5) @@CONNECTIONS
The @@CONNECTIONS variable returns the no of attempted connections,either successful or unsuccessful since SQL was last started.
6) @@ROWCOUNT
The @@ROWCOUNT returns the number of rows affected by the last statement.
7) @@TOTAL_READ
The @@TOTAL_READ returns the number of disk reads, not cache reads, by SQL Server since SQL Server was last started.
8) @@TOTAL_WRITE
The @@TOTAL_WRITE returns the number of disk writes by SQL Server since SQL Server was last started.
9) @@TRANCOUNT
The variable @@TRANCOUNT returns the number of Begin transactions statements in a given connection.
i.e. for every BEGIN TRAN / BEGIN TRANSACTION statement the variable gets incremented by 1.
The variable gets reset to zero if the transactions are committed or rollback.
Example:
Begin Tran
Insert Into Emp_Details Values (1,'alpha')
Begin Tran
Insert Into Emp_Details Values (2,'bravo')
Print @@TRANCOUNT //Prints 2 since two transactions statement are active
Rollback Tran
Print @@TRANCOUNT //Prints 0 since all the tansactions are roll backed and hence no active transactions
Output:
2
1
1) @@VERSION
We can use the @@VERSION system variable to determine product name, level, version, processor architecture(32 bit or 64 bit),
build date, and OS for the current installation of SQL Server.
Eg: Select @@VERSON
returns -
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2009 15:48:46 Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1
here,
First line gives the SQL Server version name and the realease type
here Microsoft SQL Server 2008 R2 is the product name
RTM gives the product level
10.50.1600.1 here indicates the product version.
Second line gives the installation date.
Third line indicates the Processor architecture and edition of the SQL Server.
here Enterprise Edition gives the product edition
2)sp_spaceused
The stored procdedure sp_spacedused gives the space used, no of records, index size etc used by a particular table .
Eg: sp_spaceused tbl_Emp_master
returns -
name rows reserved data index_size unused
tbl_Emp_master 63732 68640 KB 61600 KB 6088 KB 952 KB
the above sp can be used to find the details about a database also by just using the sp name that is sp_spaceused and executing.
3)@@SERVERNAME
The @@SERVERNAME variable returns the machine name on which SQL Server is running.
4) @@MAX_CONNECTIONS
The @@MAX_CONNECTIONS variable returns the maximum number of connections allowed simultaneously to the SQL Server.
5) @@CONNECTIONS
The @@CONNECTIONS variable returns the no of attempted connections,either successful or unsuccessful since SQL was last started.
6) @@ROWCOUNT
The @@ROWCOUNT returns the number of rows affected by the last statement.
7) @@TOTAL_READ
The @@TOTAL_READ returns the number of disk reads, not cache reads, by SQL Server since SQL Server was last started.
8) @@TOTAL_WRITE
The @@TOTAL_WRITE returns the number of disk writes by SQL Server since SQL Server was last started.
9) @@TRANCOUNT
The variable @@TRANCOUNT returns the number of Begin transactions statements in a given connection.
i.e. for every BEGIN TRAN / BEGIN TRANSACTION statement the variable gets incremented by 1.
The variable gets reset to zero if the transactions are committed or rollback.
Example:
Begin Tran
Insert Into Emp_Details Values (1,'alpha')
Begin Tran
Insert Into Emp_Details Values (2,'bravo')
Print @@TRANCOUNT //Prints 2 since two transactions statement are active
Rollback Tran
Print @@TRANCOUNT //Prints 0 since all the tansactions are roll backed and hence no active transactions
Output:
2
1
