Getting SQL Server Version

Hello Dev, are you looking for a way to retrieve the version of SQL Server running on your system? Here are some easy methods that you can use to get SQL Server version, whether it’s through T-SQL script, Registry or Management Studio. Let’s dive into each option in detail.

Using T-SQL Script

If you have access to a SQL Server instance, you can use a T-SQL script to get its version number. Here’s how you can do it:

These commands will provide you with different details about the SQL Server version, so you can pick the one that suits your needs.

How to Use T-SQL Script

To use T-SQL script to get SQL Server version, open SQL Server Management Studio and connect to the instance that you want to check. Then, open a new query window and enter one of the commands mentioned above.

Executing the script will display the version information in the output window. You can also copy the results and paste them into a text file for future reference.

FAQ – T-SQL Scripts

Q. Can I use T-SQL script to get the version of a remote SQL Server instance?

A. Yes, you can use T-SQL script to get the version of a remote SQL Server instance. Just make sure that you have proper access and connectivity to the instance.

Q. How do I know which edition of SQL Server I am running?

A. Use the command “SELECT SERVERPROPERTY(‘edition’);” to get the edition of SQL Server.

Using Registry

Another way to get SQL Server version is to check the Windows Registry. The SQL Server version is stored in the registry as a string value.

How to Use Registry

To check the registry for the SQL Server version, follow these steps:

1. Open the Registry Editor by typing “regedit” in the Windows search bar and hitting Enter.
2. Go to the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLXX.
   Here, XX is the version number of the SQL Server instance (e.g., 2012, 2014, 2016).
3. The version number is stored in the “CurrentVersion” string value.

You can also use a script to retrieve the SQL Server version from the registry. Here’s an example:

DECLARE @InstanceName VARCHAR(100), @RegistryPath VARCHAR(100), @SqlMajorVersion VARCHAR(10), @SqlMinorVersion VARCHAR(10)
SET @InstanceName = ‘MSSQLSERVER’
SET @RegistryPath = ‘SOFTWARE\Microsoft\Microsoft SQL Server\’
EXEC xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@RegistryPath + CONVERT(VARCHAR(10), SERVERPROPERTY(‘InstanceDefaultData’)), @value_name=’CurrentVersion’, @value=@SqlVersion OUTPUT
SELECT @SqlMajorVersion = LEFT(@SqlVersion, CHARINDEX(‘.’, @SqlVersion)-1), @SqlMinorVersion = REPLACE(RIGHT(@SqlVersion, CHARINDEX(‘.’, REVERSE(@SqlVersion))-1),’.’,”)
SELECT @SqlVersion = @SqlMajorVersion + ‘.’ + @SqlMinorVersion
SELECT @SqlVersion AS ‘SQL Server Version’

FAQ – Registry

Q. Is it safe to modify the registry to check SQL Server version?

A. No, it’s not recommended to modify the registry if you are not familiar with it. Modifying the registry can cause serious system problems if done incorrectly.

Q. Can I check the version of a SQL Server instance that is not installed on my computer?

A. No, you cannot check the version of a SQL Server instance that is not installed on your computer by using the registry.

Using Management Studio

If you have SQL Server Management Studio installed, you can also use it to check the version of SQL Server.

How to Use Management Studio

To check the SQL Server version using Management Studio, follow these steps:

1. Open SQL Server Management Studio on your computer.
2. Connect to the SQL Server instance that you want to check.
3. Right-click on the instance name in Object Explorer and select “Properties”.
4. The version number is displayed in the “Product” field.

You can also use the “@@VERSION” T-SQL script in Management Studio to get more detailed information about the SQL Server version.

FAQ – Management Studio

Q. Can I use Management Studio to check the version of a remote SQL Server instance?

A. Yes, you can use Management Studio to check the version of a remote SQL Server instance. Just make sure that you have proper access and connectivity to the instance.

Q. Is Management Studio free to use?

A. Yes, SQL Server Management Studio is a free tool that can be downloaded from Microsoft’s website.