How to get detailed information of a SQL Server (T-SQL)

Introduction

This T-SQL script will demo how to get detailed information of a SQL server, including SQL server version and the installation path, number of CPU, path of all user database and log files of all databases.

Scenarios

As many people ask how to get general information of SQL Server, this script will demo how to get detailed information of a SQL server, including SQL server version and the installation path, number of CPU, path of all user database and log files of all databases.

Script

You can use this script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.
2. Copy the code from GetSQLServerInformation.sql, paste it in a new query and run the script.

After the script finishes running, we抣l get the following figure:

Here are some code snippet for your reference:

IF (@Major=9) 
    BEGIN    
        SET @Path = NULL
        EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup', 'SQLPath', @Path OUTPUT
        SELECT @Path AS [SQL Server 2005 path]
    END
    IF (@Major=10)
    BEGIN    
        SET @Path = NULL
        EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\ClientSetup', 'SQLPath', @Path OUTPUT
        SELECT @Path AS [SQL Server 2008/2008 R2 path]
    END
    IF (@Major=11)
    BEGIN    
        SET @Path = NULL
        EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\ClientSetup', 'SQLPath', @Path OUTPUT
        SELECT @Path AS [SQL Server 2012 path]
    END

IF (@Major=9)  
    BEGIN     
        SET @Path = NULL 
        EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup', 'SQLPath', @Path OUTPUT 
        SELECT @Path AS [SQL Server 2005 path] 
    END 
    IF (@Major=10) 
    BEGIN     
        SET @Path = NULL 
        EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\ClientSetup', 'SQLPath', @Path OUTPUT 
        SELECT @Path AS [SQL Server 2008/2008 R2 path] 
    END 
    IF (@Major=11) 
    BEGIN     
        SET @Path = NULL 
        EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\ClientSetup', 'SQLPath', @Path OUTPUT 
        SELECT @Path AS [SQL Server 2012 path] 
    END

SQL Server 2005 or higher version

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.