locked
How to get Host name and SQL Instance Name by T-SQL RRS feed

  • Question

  • I am using SQL 2000, 2005 and 2008. I have few multi instances SQL servers. I would like to gather following information:

    Host Name

    SQL Instance Name

    SQL Version

    Server1

    Server1

    SQL 2000

    Server1

    Server1\ABC

    SQL 2005

    Server1

    Server1\XYZ

    SQL 2008

    Server2

    Server2

    SQL 2008

    Is there any way I can get it by T-SQL? Any help would be appreciated.

    Thanks

    Saumen 

    Thursday, June 14, 2012 11:50 PM

Answers

  • SELECT
                SERVERPROPERTY('MachineName') AS [ServerName], 
    			SERVERPROPERTY('ServerName') AS [ServerInstanceName], 
                SERVERPROPERTY('InstanceName') AS [Instance], 
                SERVERPROPERTY('Edition') AS [Edition],
                SERVERPROPERTY('ProductVersion') AS [ProductVersion], 
    			Left(@@Version, Charindex('-', @@version) - 2) As VersionName


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    • Proposed as answer by Syed Qazafi Anjum Friday, June 15, 2012 12:06 AM
    • Marked as answer by XYZ001 Friday, June 15, 2012 12:08 AM
    Friday, June 15, 2012 12:03 AM

All replies

  • I don't think you can get it by T-SQL query.Please refer to the below link.

    http://learningsqlserver.wordpress.com/2011/01/21/what-version-of-sql-server-do-i-have/


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you


    • Edited by Eshani Rao Friday, June 15, 2012 12:02 AM
    • Proposed as answer by gbf2 Tuesday, April 18, 2017 3:24 PM
    • Unproposed as answer by gbf2 Tuesday, April 18, 2017 3:24 PM
    Friday, June 15, 2012 12:01 AM
  • SELECT
                SERVERPROPERTY('MachineName') AS [ServerName], 
    			SERVERPROPERTY('ServerName') AS [ServerInstanceName], 
                SERVERPROPERTY('InstanceName') AS [Instance], 
                SERVERPROPERTY('Edition') AS [Edition],
                SERVERPROPERTY('ProductVersion') AS [ProductVersion], 
    			Left(@@Version, Charindex('-', @@version) - 2) As VersionName


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    • Proposed as answer by Syed Qazafi Anjum Friday, June 15, 2012 12:06 AM
    • Marked as answer by XYZ001 Friday, June 15, 2012 12:08 AM
    Friday, June 15, 2012 12:03 AM
  • Thank you Steven. I needed to get the name of the SQL Server host machine. SELECT @@SERVERNAME did not get it done on a VM platform.


    Thursday, July 10, 2014 10:00 PM
  • Built-in function HOST_NAME() returns the name of the host machine where you are connecting from. It seems this is the missing piece of your puzzle.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/host-name-transact-sql



    • Edited by DaveBolt Tuesday, March 27, 2018 3:28 PM
    Tuesday, March 27, 2018 3:28 PM
  • Keep in mind that the HOST_NAME command was added in SQL Server 2016.
    Thursday, April 9, 2020 4:31 AM