[MSSQL] 11. 인스턴스 구성 정보 조회 쿼리

1. Machine Name

2. Instance Name

3. Server Name

4. Service Port

5. Service Account

6. Collation

7. Data Root Directory

8. CPU / Memory Spec

9. DBMS Version

 

위 메타 정보를 조회하는 쿼리입니다.

DECLARE @HkeyLocal NVARCHAR(18)
DECLARE @Instance1 VARCHAR(100)
DECLARE @Instance2 VARCHAR(100)
DECLARE @MSSqlServerRegPath NVARCHAR(200)
DECLARE @PortNumber NVARCHAR(100)
DECLARE @SQLDataRoot NVARCHAR(100)
DECLARE @SQLPath NVARCHAR(100)
DECLARE @SqlProgramDir NVARCHAR(100)
DECLARE @Collation NVARCHAR(128)
DECLARE @MachineName NVARCHAR(128)
DECLARE @ServiceAccount NVARCHAR(128)

SET @Instance1 = ISNULL(convert(VARCHAR(128), serverproperty('InstanceName')), 'MSSQLSERVER')
SET @Collation = convert(NVARCHAR(128), serverproperty('Collation'))
SET @MachineName = convert(NVARCHAR(128), serverproperty('MachineName'))
SET @HkeyLocal = N'HKEY_LOCAL_MACHINE'
SET @MSSqlServerRegPath = N'SOFTWARE\Microsoft\\Microsoft SQL Server\\Instance Names\SQL'

EXEC xp_instance_regread @HkeyLocal
	,@MSSqlServerRegPath
	,@Instance1
	,@Instance2 OUTPUT

SET @MSSqlServerRegPath = N'SOFTWARE\Microsoft\\Microsoft SQL Server\' + @Instance2 + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'

EXEC xp_instance_regread @HkeyLocal
	,@MSSqlServerRegPath
	,N'TcpPort'
	,@PortNumber OUTPUT

SET @MSSqlServerRegPath = N'SOFTWARE\Microsoft\\Microsoft SQL Server\' + @Instance2 + '\Setup'

EXEC xp_instance_regread @HkeyLocal
	,@MSSqlServerRegPath
	,N'SQLDataRoot'
	,@SQLDataRoot OUTPUT

IF @Instance1 = 'MSSQLSERVER'
	SET @MSSqlServerRegPath = N'SYSTEM\CurrentControlSet\Services\' + @Instance1
ELSE
	SET @MSSqlServerRegPath = N'SYSTEM\CurrentControlSet\Services\MSSQL$' + @Instance1

EXEC xp_instance_regread @HkeyLocal
	,@MSSqlServerRegPath
	,N'ObjectName'
	,@ServiceAccount OUTPUT

SELECT 'Machine Name' AS [Key],@MachineName AS Value
UNION ALL
SELECT 'Instance Name' AS [Key],@instance1 AS Value
UNION ALL
SELECT 'ServerName' AS [Key],@@SERVERNAME AS Value
UNION ALL
SELECT 'Service Port' AS [Key],@PortNumber AS Value
UNION ALL
SELECT 'Service Account' AS [Key],@ServiceAccount AS Value
UNION ALL
SELECT 'Collation' AS [Key],@Collation AS Value
UNION ALL
SELECT 'Data Root' AS [Key]	,@SQLDataRoot AS Value
UNION ALL
SELECT 'CPU and Memory'	,'cpu_count: ' + convert(VARCHAR(128), cpu_count) + ', hyperthread_ratio:' + convert(VARCHAR(128), hyperthread_ratio) + ', physical_memory(MB):' + convert(VARCHAR(128), physical_memory_kb / (1024))
FROM sys.dm_os_sys_info
UNION ALL
SELECT 'Version' AS [Key],@@VERSION AS Value
GO