In this article, we will show you how to get SQL Server instance information using various methods.
Method 1: Using the “sp_instance_registry” System Stored Procedure
You can use the “sp_instance_registry” system stored procedure to return the instances of SQL Server that are registered with the current Windows operating system. This stored procedure is only available on SQL Server 2012 and later versions.
To use this method, you need to have sysadmin privileges. You can execute the following T-SQL script to get the instance name and the version of each instance:
USE master;
GO
EXEC sp_instance_registry;
GO
Method 2: Using the “sys.dm_os_ring_buffers” Dynamic Management View
You can use the “sys.dm_os_ring_buffers” dynamic management view to return information about all SQL Server instances that are running on the machine. This view is available on all versions of SQL Server. To use this method, you need to have VIEW SERVER STATE permission. You can execute the following T-SQL script to get the instance name and the version of each instance:
SELECT
instance_name,
version
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ‘RING_BUFFER_CONNECTIVITY’
GO
Method 3: Using the “SERVERPROPERTY” Function
You can use the SERVERPROPERTY function to return information about the current SQL Server instance. This function is available on all versions of SQL Server. To use this method, you need to have VIEW SERVER STATE permission. You can execute the following T-SQL script to get the instance name and the version of each instance:
SELECT
SERVERPROPERTY(‘instance name) AS InstanceName,
SERVERPROPERTY(‘productversion’) AS ProductVersion,
SERVERPROPERTY(‘product level) AS ProductLevel;
GO
Method 4: Using the “REGISTRY” Function
You can use the REGISTRY function to return information about the current SQL Server instance from the registry. This function is available on all versions of SQL Server. To use this method, you need to have SELECT permission on the registry keys that are listed in the following table. You can execute the following T-SQL script to get the instance name and the version of each instance:
SELECT
SERVERPROPERTY(‘instance name) AS InstanceName,
SERVERPROPERTY(‘productversion’) AS ProductVersion,
SERVERPROPERTY(‘product level) AS ProductLevel;
GO
Method 5: Using the “SUSER_SNAME” Function
You can use the SUSER_SNAME function to return information about the current SQL Server instance from the security catalog views. This function is available on all versions of SQL Server. To use this method, you need to have VIEW DEFINITION permission on the sys.server_principals catalog view. You can execute the following T-SQL script to get the instance name and the version of each instance:
SELECT
SUSER_NAME(SERVERPRINCIPALID) AS InstanceName,
SERVERPROPERTY(‘productversion’) AS ProductVersion,
SERVERPROPERTY(‘product level) AS ProductLevel;
GO
Note: The methods that are described in this article only return information about the SQL Server instances that are running on the local machine. If you want to get information about the SQL Server instances that are running on a remote machine, you need to use one of the following methods:
Method 6: Using the “SQL Browser” Service
If the SQL Browser service is running on the remote machine, you can use it to enumerate theSQL Server instances that are running on that machine. To use this method, you need to haveVIEW SERVER STATE permission. You can execute the following T-SQL script to get the instance name and the version of each instance:
DECLARE
@SQLBrowserServiceName nvarchar(max) = N”,
@SQLBrowserServiceStatus nvarchar(max) = N”;
— Get the name and the status of the SQL Browser service
SELECT
@SQLBrowserServiceName = ServiceName,
@SQLBrowserServiceStatus = ServiceStateDesc
FROM sys.dm_server_services
WHERE ServiceType = 4 — SQL Browser service type is 4
AND IsRunning = 1; — Only get services that are running
IF (@SQLBrowserServiceName <> N”)
BEGIN
DECLARE
@InstanceName nvarchar(max) = N”,
@Version nvarchar(max) = N”;
— Get the instance name and the version of each instance
SELECT
@InstanceName = InstanceName,
@Version = Version
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ‘RING_BUFFER_CONNECTIVITY’
GO
END
Conclusion:
This article has described five methods that you can use to get information about the SQL Server instances that are running on a local or a remote machine. You can use any of these methods depending on your requirements.
In this article, you have seen five different methods that you can use to get information about the SQL Server instances that are running on a machine. You can use any of these methods depending on your requirements.