847
Are you new to Microsoft SQL Server and not sure which instance is installed on your computer or server? You may have multiple instances installed on the same machine, and it is crucial to know which one you are working with. In this blog post, let’s use a T-SQL script to quickly and easily discover the SQL Server instance installed on your machine.
T-Sql
--TSql script to return all the SQL Server instances installed on your machine
DECLARE @InstalledInstances TABLE -- Create a table variable to store the registry query results
(
[Value] NVARCHAR(100),
InstanceName NVARCHAR(100),
[Data] NVARCHAR(100)
)
INSERT INTO @InstalledInstances -- Populate the table variable with the registry query results
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'
SELECT
InstanceName
--, *
FROM @InstalledInstances -- Select the InstanceName column from the table variable to return the SQL Server instance names
Using the built-in Properties and Variables
T-Sql
--T-SQL script using server property
SELECT SERVERPROPERTY('SERVERNAME') AS 'ServerName', SERVERPROPERTY ('INSTANCENAME') AS 'InstanceName'
--T-SQL script using a variable
SELECT @@SERVERNAME AS 'ServerName', @@SERVICENAME AS 'InstanceName'
Using a Command line
If you don’t have SQL Server Management Studio (SSMS) installed on your machine, or if you can’t access it for some reason, you can still get a list of the SQL Server instances installed on the machine using the Command Prompt. To do this, open the Windows Command Prompt and enter the following command: SQLCMD -L.
Note that the ‘L‘ must be capitalized.
