Find Server property using TSQL code

Retrieving SQL Server Information Using T-SQL

In this article, we will discuss how to retrieve information about your installed SQL Server instance using a T-SQL query that interacts with internal system objects. This query can provide various details, such as the service account, paths, configuration settings, and much more.

Query Overview

The following T-SQL query retrieves multiple configuration settings and properties from SQL Server. It covers information such as the server's version, instance details, backup paths, and various registry settings. This is useful for system administrators and those who need to audit or document SQL Server environments.

The query returns a result in two columns: one with the name of the configuration setting and the other with its value.

Query Explanation

The query starts by declaring a series of variables that will hold the registry paths and values related to SQL Server configuration.

DECLARE @HkeyLocal NVARCHAR(18),
        @ServicesRegPath NVARCHAR(34),
        @SqlServiceRegPath SYSNAME,
        @BrowserServiceRegPath SYSNAME,
        @MSSqlServerRegPath NVARCHAR(31),
        @InstanceNamesRegPath NVARCHAR(59),
        @InstanceRegPath SYSNAME,
        @SetupRegPath SYSNAME,
        @NpRegPath SYSNAME,
        @TcpRegPath SYSNAME,
        @RegPathParams SYSNAME,
        @FilestreamRegPath SYSNAME;

The paths for various registry keys related to SQL Server configuration are then set:

SELECT @HkeyLocal = N'HKEY_LOCAL_MACHINE';

-- Instance-based paths
SELECT @MSSqlServerRegPath = N'SOFTWARE\\Microsoft\\MSSQLServer';
SELECT @InstanceRegPath = @MSSqlServerRegPath + N'\\MSSQLServer';
SELECT @FilestreamRegPath = @InstanceRegPath + N'\\Filestream';
SELECT @SetupRegPath = @MSSqlServerRegPath + N'\\Setup';
SELECT @RegPathParams = @InstanceRegPath + '\\Parameters';

-- Services
SELECT @ServicesRegPath = N'SYSTEM\\CurrentControlSet\\Services';
SELECT @SqlServiceRegPath = @ServicesRegPath + N'\\MSSQLSERVER';
SELECT @BrowserServiceRegPath = @ServicesRegPath + N'\\SQLBrowser';

-- InstanceId setting
SELECT @InstanceNamesRegPath = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL';

-- Network settings
SELECT @NpRegPath = @InstanceRegPath + N'\\SuperSocketNetLib\\Np';
SELECT @TcpRegPath = @InstanceRegPath + N'\\SuperSocketNetLib\\Tcp';

Fetching Configuration Details

The query then retrieves various configuration values using the xp_instance_regread extended stored procedure. Some of the configuration details include:

• Audit level
• Number of error logs
• Login mode
• Mail profile
• Backup directory

For example, the following code fetches the audit level:

DECLARE @SmoAuditLevel INT;
EXEC master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'AuditLevel', @SmoAuditLevel OUTPUT;

File Paths and Error Logs

The query continues by retrieving file paths for the master database, error logs, and other important system directories:

DECLARE @MasterPath NVARCHAR(512),
        @LogPath NVARCHAR(512),
        @ErrorLog NVARCHAR(512),
        @ErrorLogPath NVARCHAR(512);

SELECT @MasterPath = SUBSTRING(physical_name, 1, LEN(physical_name) - CHARINDEX('\\', REVERSE(physical_name)))
FROM master.sys.database_files WHERE name = N'master';

SELECT @LogPath = SUBSTRING(physical_name, 1, LEN(physical_name) - CHARINDEX('\\', REVERSE(physical_name)))
FROM master.sys.database_files WHERE name = N'mastlog';

SELECT @ErrorLog = CAST(SERVERPROPERTY(N'errorlogfilename') AS NVARCHAR(512));
SELECT @ErrorLogPath = SUBSTRING(@ErrorLog, 1, LEN(@ErrorLog) - CHARINDEX('\\', REVERSE(@ErrorLog)));

Extracting Additional Information

The query also retrieves additional server and instance details, including the service account name, whether named pipes and TCP are enabled, and more.

DECLARE @ServiceAccount NVARCHAR(512);
EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'ObjectName', @ServiceAccount OUTPUT;

DECLARE @NamedPipesEnabled INT;
EXEC master.dbo.xp_instance_regread @HkeyLocal, @NpRegPath, N'Enabled', @NamedPipesEnabled OUTPUT;

DECLARE @TcpEnabled INT;
EXEC master.sys.xp_instance_regread @HkeyLocal, @TcpRegPath, N'Enabled', @TcpEnabled OUTPUT;

Retrieving Server and Instance Properties

The final output includes several important properties, such as the version of SQL Server, the server collation, and whether SQL Server is clustered:

SELECT
  SERVERPROPERTY(N'ProductVersion') AS [VersionString],
  CAST(SERVERPROPERTY(N'Edition') AS SYSNAME) AS [Edition],
  CAST(SERVERPROPERTY(N'ProductLevel') AS SYSNAME) AS [ProductLevel],
  SERVERPROPERTY('Collation') AS [Collation],
  CAST(SERVERPROPERTY('IsClustered') AS BIT) AS [IsClustered];

Query Result

The query outputs a list of configuration names along with their corresponding values. Here is an example of the result structure:

• Audit Level: 1
• Number of Error Logs: 10
• Login Mode: 2
• Mail Profile: NULL
• Backup Directory: C:\Backup

Conclusion

This T-SQL query is a valuable tool for retrieving SQL Server configuration details from system registry entries and properties. By executing this query, administrators can gain insight into the SQL Server instance’s configuration without needing to manually inspect the system or configuration files.