How to Get Instance Name from T-SQL

• Arun-644923

  SSC-Addicted

  Points: 486

  More actions

  July 14, 2008 at 3:12 am

  #381653

  Hi All,

  How to get Current Instance Name of SQL Server from T-SQL (i.e. from Query window)?

  Thanks in advance,

  Regards,

  Arun

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  July 14, 2008 at 3:19 am

  #842243

  Hey,

  I am not sure if this is what you were looking for... but I can run this query to see the current server and all the linked servers on this instance of the server.

  select * from sys.sysservers

  Regards,

  Prakash.P

  ---------------------------------------------------------------------------------

• Arun-644923

  SSC-Addicted

  Points: 486

  More actions

  July 14, 2008 at 3:24 am

  #842245

  Hi,

  Thanks for your update.

  Yes. That's what I am looking for.

  Thanks,

  Regards,

  Arun

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  July 14, 2008 at 3:26 am

  #842247

  SELECT SERVERPROPERTY ('InstanceName')

  "Keep Trying"

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  July 14, 2008 at 4:31 am

  #842289

  SELECT @@SERVERNAME

• Hari.Sharma

  SSCertifiable

  Points: 6904

  More actions

  July 14, 2008 at 5:04 am

  #842295

  --1.

  SELECT SERVERPROPERTY('InstanceName')

  --2

  sp_helpserver

  --3

  select @@SERVERNAME

  --4

  SELECT * FROM SYS.SYSSERVERS

  --5

  SELECT * FROM SYS.SERVERS

  Cheers,
  Hari
  Tips & Tricks for SQL BI Developers

• ashwin4all1

  Ten Centuries

  Points: 1131

  More actions

  July 10, 2009 at 10:57 am

  #1023217

  Hi

  All,

  You better To use the following command for Getting the instance name.

  EXECUTE xp_regread @rootkey='HKEY_LOCAL_MACHINE',

  @key='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQl',

  @value_name='MSSQLSERVER'

  Thanks and regards

  Ashwin v p

  Ashwin VP
  CSC India ...

• mcTDBA

  Default port

  Points: 1458

  More actions

  October 25, 2010 at 12:47 pm

  #1241835

  I use this.

  Select Case

  When SERVERPROPERTY ('InstanceName') Is Null Then @@SERVERNAME

  Else SERVERPROPERTY ('InstanceName')

  End

• R.P.Rozema

  SSChampion

  Points: 12317

  More actions

  October 26, 2010 at 12:29 am

  #1242046

  Have a look at what Books online has to say about @@servername and serverproperty(). My recommendation is to use consistently only select convert(nvarchar(128), serverproperty('servername')); Mixing the various alternatives for getting your server's name is a sure way to run into problems.

  ---

  
  Posting Data Etiquette - Jeff Moden[/url]
  Posting Performance Based Questions - Gail Shaw[/url]
  Hidden RBAR - Jeff Moden[/url]
  Cross Tabs and Pivots - Jeff Moden[/url]
  Catch-all queries - Gail Shaw[/url]
  

  ---

  
  If you don't have time to do it right, when will you have time to do it over?

• feersum_endjinn

  SSCommitted

  Points: 1966

  More actions

  October 26, 2010 at 4:14 am

  #1242123

  To get the "instance" name you can also do:

  select @@ServiceName

• a.zimnik

  SSC Rookie

  Points: 49

  More actions

  June 28, 2011 at 3:57 am

  #1345038

  @@ServiceName would be ok if there are named instances.

  If none is available then select @@servicename results MSSQLSERVER.

  So if you are interested in only the instance name, without the servername,

  and replace instance name with servername, if there is no named instance then you could script:

  HAVE FUN WITH IT.

  declare @nameServer varchar(100)

  declare @nameMachine varchar(50)

  declare @kInstance varchar(50)

  declare @constrServer varchar(100)

  select @nameServer = convert(nvarchar(128), serverproperty('servername'));

  select @nameMachine = convert(nvarchar(128), serverproperty('machinename'));

  if len(@nameServer) = len(@nameMachine)

  select @kInstance = '' -- 'NN'

  else

  select @kInstance = right(@nameServer, len(@nameServer) - (len(@nameMachine)+1));

  -- construct name of server again from 2 variables:

  if @kInstance='' select @constrServer = @nameMachine

  else select @constrServer = @nameMachine + '\' + @kInstance;

  select @nameServer as Sname, @nameMachine as Mname, @kInstance as kInst, @constrServer as cServ

  -- if you are connected to a no named instance,

  -- then this Qry should present you with the columns Sname, Mname and cServ as identical, kInst should be empty.

  -- if you are connected to a named instance, then

  -- this Qry should result in

  -- Sname and cServ are identical

  -- Mname is the machine name

  -- kInst is the named instance.

• tgb25nld

  SSC Journeyman

  Points: 89

  More actions

  November 7, 2011 at 12:21 pm

  #1404135

  ashwin4all1 (7/10/2009)

  ---

  Hi

  All,

  You better To use the following command for Getting the instance name.

  EXECUTE xp_regread @rootkey='HKEY_LOCAL_MACHINE',

  @key='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQl',

  @value_name='MSSQLSERVER'

  Thanks and regards

  Ashwin v p

  This is dead on the mark! Elegant and precise - thank you very much

  Ted Bailey

  Hospice of the Western Reserve

  Cleveland, Ohio USA

• Jeff Moden

  SSC Guru

  Points: 1004280

  More actions

  November 7, 2011 at 5:17 pm

  #1404221

  ashwin4all1 (7/10/2009)

  ---

  Hi

  All,

  You better To use the following command for Getting the instance name.

  EXECUTE xp_regread @rootkey='HKEY_LOCAL_MACHINE',

  @key='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQl',

  @value_name='MSSQLSERVER'

  Thanks and regards

  Ashwin v p

  Why is that better?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Rao.V

  Hall of Fame

  Points: 3452

  More actions

  November 15, 2011 at 10:07 pm

  #1408251

  ChiragNS (7/14/2008)

  ---

  SELECT SERVERPROPERTY ('InstanceName')

  this helped me.. thanks

• Armando V.

  SSC Rookie

  Points: 45

  More actions

  January 30, 2013 at 9:38 am

  #1582647

  Really great solution !

  a.zimnik (6/28/2011)

  ---

  @@ServiceName would be ok if there are named instances.

  If none is available then select @@servicename results MSSQLSERVER.

  So if you are interested in only the instance name, without the servername,

  and replace instance name with servername, if there is no named instance then you could script:

  HAVE FUN WITH IT.

  declare @nameServer varchar(100)

  declare @nameMachine varchar(50)

  declare @kInstance varchar(50)

  declare @constrServer varchar(100)

  select @nameServer = convert(nvarchar(128), serverproperty('servername'));

  select @nameMachine = convert(nvarchar(128), serverproperty('machinename'));

  if len(@nameServer) = len(@nameMachine)

  select @kInstance = '' -- 'NN'

  else

  select @kInstance = right(@nameServer, len(@nameServer) - (len(@nameMachine)+1));

  -- construct name of server again from 2 variables:

  if @kInstance='' select @constrServer = @nameMachine

  else select @constrServer = @nameMachine + '\' + @kInstance;

  select @nameServer as Sname, @nameMachine as Mname, @kInstance as kInst, @constrServer as cServ

  -- if you are connected to a no named instance,

  -- then this Qry should present you with the columns Sname, Mname and cServ as identical, kInst should be empty.

  -- if you are connected to a named instance, then

  -- this Qry should result in

  -- Sname and cServ are identical

  -- Mname is the machine name

  -- kInst is the named instance.