In this article, I will provide you the possible ways to find or check Microsoft sql server version, service pack etc installed on your pc/server. This is helpful for person like DBA and who want's to upgrade SQL server now, so to upgrade you must first know the current version and details of your service pack, so in that case this article would be helpful for you.
Although it shouldn't be difficult for anyone to find details of version but you should know the below listed various easy ways to determine it quickly.
- Checking SQL server version using various methods
- 1. Check sql server version using @@VERSION query
- 2. Check SQL server version using ServerProperty in query
- 3. Check SQL server version using system extended stored procedure
- 4. Check SQL server version using SQL server properties
- 5. Check sql server version using the Windows Command Prompt
- 6. Check SQL Server Version SQL Cmd Utility
Checking SQL server version using various methods
Now, I will explain possible ways to determine sql server version and service pack, let's get started with it.
1. Check sql server version using @@VERSION query
Start your SQL server management studio, connect to the server and right-click on your Server, click "New Query", and excute the below query
Output must be as below
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
as you can see in the above image we are getting complete details of our SQL server version which is 2012, it is 64-bit version.
You can also use below SQL Query to check sql server version
declare @sqlVers numeric(4,2) select @sqlVers = left(cast(serverproperty('productversion') as varchar), 4)
You will get output as 8.00, 9.00, 10.00 and 10.50, 11 for SQL 2000, 2005, 2008, 2008R2 and 2012 respectively
2. Check SQL server version using ServerProperty in query
Well this is another method to determine sql server version in detail running scripts, here is the sample script which you can run your SQL server Management studio
SELECT SERVERPROPERTY('MachineName') AS ComputerName, SERVERPROPERTY('ServerName') AS InstanceName, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel; GO
Output will be in tabular form with specific details which we have selected in the above query
ComputerName InstanceName Edition ProductVersion ProductLevel
DESKTOP-1PM1CJ9 DESKTOP-1PM1CJ9\SQLEXPRESS2 Express Edition (64-bit) 11.0.2100.60 RTM
There are many more Server properties which you can get using the ServerProperty query in SQL server, here is the complete list for SERVERPROPERTY
3. Check SQL server version using system extended stored procedure
This is another good method which returns all the details of your SQL server version, try executing the master database stored procedure, in my case it was located in System database-> master -> Programmability -> sys.sp_server_info
Executing this stored procedure, as below
provide me lots of information in single query
4. Check SQL server version using SQL server properties
This is probably one of the easiest method to find out SQL server details. In this method we will check SQL server version through sqlservr properties.Here is the list of procedure you need to follow in this way to get all the details
- Navigate to C:\Program Files\Microsoft SQL Server\YourServer.SQLExpress\MSSQL\Binn (in my case it was C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2\MSSQL\Binn)
- look for sqlservr executable file
- Right click the file and click Properties.
- Click Details tab
- Find product version and product name listed.
Here is the output for me
5. Check sql server version using the Windows Command Prompt
To determine what edition of SQL you are running, do the following:
- Open a command prompt windows on the machine SQL is installed to (Start > Run, type cmd, hit enter)
- Type SQLCMD -S servername\instancename (where servername = the name of your server, and instancename is the name of the SQL instance).The prompt will change to 1>
- Type select @@version and hit <ENTER>.
- At the 2> prompt type go and hit <ENTER>.
This will return the SQL version that is running on your server. If you have multiple instances repeat the process for each instance.
The servername mentioned above will be the name of the machine SQL is installed to. If you are unsure of your instance name do the following:
- Open a command prompt window as described above.
- Type services.msc.
- Browse down to entries beginning with SQL.
- There will be an entry for each instance called SQL Server (instancename).
- Whatever appears in the parentheses is your instance name.
6. Check SQL Server Version SQL Cmd Utility
SQLCMD is a part of the SQL Server Client Tools, you can use it also to check sql server version
sqlcmd.exe -S ServerName\InstanceName -E -Q "SELECT @@VERSION"
You may also like :
That's it, we are done, these were some of the widely used ways to check version of SQL Server.