How to get current instance name from T-SQL

Question

How can I get the SQL Server server and instance name of the current connection  using a T-SQL script

User · Answer

SELECT   servername will give you data as server instanceName  To get only the instanceName you should run select   ServiceName query

User · Answer

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     http   technet microsoft com en-us library ms174396 aspx

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How to get current instance name from T-SQL

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008r2-express