How to get current instance name from T-SQL
SqlSql ServerTsqlSql Server-2008r2-ExpressSql Problem Overview
How can I get the SQL Server server and instance name of the current connection, using a T-SQL script?
Sql Solutions
Solution 1 - Sql
Just found the answer, in this SO question (literally, inside the question, not any answer):
SELECT @@servername
returns servername\instance as far as this is not the default instance
SELECT @@servicename
returns instance name, even if this is the default (MSSQLSERVER)
Solution 2 - Sql
How about this:
EXECUTE xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQl',
@value_name='MSSQLSERVER'
This will get the instance name as well. null
means default instance:
SELECT SERVERPROPERTY ('InstanceName')
Solution 3 - Sql
SELECT @@servername
will give you data as server/instanceName
To get only the instanceName
you should run select @@ServiceName
query .
Solution 4 - Sql
Why stop at just the instance name? You can inventory your SQL Server environment with following:
SELECT
SERVERPROPERTY('ServerName') AS ServerName,
SERVERPROPERTY('MachineName') AS MachineName,
CASE
WHEN SERVERPROPERTY('InstanceName') IS NULL THEN ''
ELSE SERVERPROPERTY('InstanceName')
END AS InstanceName,
'' as Port, --need to update to strip from Servername. Note: Assumes Registered Server is named with Port
SUBSTRING ( (SELECT @@VERSION),1, CHARINDEX('-',(SELECT @@VERSION))-1 ) as ProductName,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,
SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion,
SERVERPROPERTY('ProductBuild') AS ProductBuild,
SERVERPROPERTY('Edition') AS Edition,
CASE SERVERPROPERTY('EngineEdition')
WHEN 1 THEN 'PERSONAL'
WHEN 2 THEN 'STANDARD'
WHEN 3 THEN 'ENTERPRISE'
WHEN 4 THEN 'EXPRESS'
WHEN 5 THEN 'SQL DATABASE'
WHEN 6 THEN 'SQL DATAWAREHOUSE'
END AS EngineEdition,
CASE SERVERPROPERTY('IsHadrEnabled')
WHEN 0 THEN 'The Always On Availability Groups feature is disabled'
WHEN 1 THEN 'The Always On Availability Groups feature is enabled'
ELSE 'Not applicable'
END AS HadrEnabled,
CASE SERVERPROPERTY('HadrManagerStatus')
WHEN 0 THEN 'Not started, pending communication'
WHEN 1 THEN 'Started and running'
WHEN 2 THEN 'Not started and failed'
ELSE 'Not applicable'
END AS HadrManagerStatus,
CASE SERVERPROPERTY('IsSingleUser') WHEN 0 THEN 'No' ELSE 'Yes' END AS InSingleUserMode,
CASE SERVERPROPERTY('IsClustered')
WHEN 1 THEN 'Clustered'
WHEN 0 THEN 'Not Clustered'
ELSE 'Not applicable'
END AS IsClustered,
'' as ServerEnvironment,
'' as ServerStatus,
'' as Comments
Solution 5 - Sql
I found this:
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'
That will give you list of all instances installed in your server.
> The ServerName
property of the SERVERPROPERTY
function and @@SERVERNAME
return similar information. The ServerName
property provides the Windows server and instance name that together make up the unique server instance. @@SERVERNAME
provides the currently configured local server name.
And Microsoft example for current server is:
SELECT CONVERT(sysname, SERVERPROPERTY('servername'));
> This scenario is useful when there are multiple instances of SQL Server installed on a Windows server, and the client must open another connection to the same instance used by the current connection.
Solution 6 - Sql
To get the list of server and instance that you're connected to:
select * from Sys.Servers
To get the list of databases that connected server has:
SELECT * from sys.databases;
Solution 7 - Sql
Just to add some clarification to the registry queries. They only list the instances of the matching bitness (32 or 64) for the current instance.
The actual registry key for 32-bit SQL instances on a 64-bit OS is:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server
You can query this on a 64-bit instance to get all 32-bit instances as well. The 32-bit instance seems restricted to the Wow6432Node so cannot read the 64-bit registry tree.
Solution 8 - Sql
another method to find Instance name- Right clck on Database name and select Properties, in this part you can see view connection properties in left down corner, click that then you can see the Instance name.