Copied from a post on SQLserverCentral.com by Gokhan Varol.
http://www.sqlservercentral.com/scripts/87561/
SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE
@SqlPath NVARCHAR(255),
@InstName VARCHAR(16) = @@SERVICENAME,
@value_name NVARCHAR(20),
@LoginMode_Value INT,
@LoginMode NVARCHAR(15),
@RegLoc VARCHAR(100),
@ProcessorInfo VARCHAR(256)
--#region Retrieve Port Number
SET @RegLoc = CASE WHEN @InstName = 'MSSQLSERVER' THEN 'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\' ELSE 'Software\Microsoft\Microsoft SQL Server\' + @InstName + '\MSSQLServer\SuperSocketNetLib\Tcp\' END
DECLARE
@RegRead TABLE(
VALUE VARCHAR(4000),
DATA VARCHAR(4000))
INSERT INTO @RegRead(
Value,
Data)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @RegLoc, 'tcpPort';
--#endregion
--#region Get processor description from Windows Registry
EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'HARDWARE\DESCRIPTION\System\CentralProcessor\0', 'ProcessorNameString', @ProcessorInfo OUTPUT, N'no_output'
--#endregion
--#region Find Service Accounts
DECLARE
@ServicePath NVARCHAR(256) = N'SYSTEM\CurrentControlSet\Services\' + CASE
WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'MSSQLSERVER'
ELSE 'MSSQL$' + @@SERVICENAME
END,
@AgentServicePath NVARCHAR(256) = N'SYSTEM\CurrentControlSet\Services\' + CASE
WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLSERVERAGENT'
ELSE 'SQLAgent$' + @@SERVICENAME
END,
@MSSQLServiceAccountName VARCHAR(256),
@SQLAgentServiceAccountName VARCHAR(256)
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @AgentServicePath, N'ObjectName', @SQLAgentServiceAccountName OUTPUT, N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @ServicePath, N'ObjectName', @MSSQLServiceAccountName OUTPUT, N'no_output'
--#endregion
--#region Retrieve Security
IF @InstName IS NULL
SET @RegLoc = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'
ELSE
SET @RegLoc = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @RegLoc, @value_name = @InstName, @value = @SqlPath OUTPUT
IF @InstName IS NULL
SET @RegLoc = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\'
ELSE
SET @RegLoc = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @sqlpath + '\MSSQLServer\'
EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @RegLoc, @value_name = 'LoginMode', @value = @LoginMode_Value OUTPUT
IF @LoginMode_Value = 1
SET @LoginMode = 'Windows'
IF @LoginMode_Value = 2
SET @LoginMode = 'Mixed'
--#endregion
--#region Get System Manufacturer and model number from
IF OBJECT_ID('tempdb..#SystemManufacturer')IS NOT NULL
DROP TABLE
#SystemManufacturer
CREATE TABLE #SystemManufacturer(
LogDate DATETIME NOT NULL,
ProcessInfor VARCHAR(256),
Text VARCHAR(MAX)NOT NULL)
INSERT INTO #SystemManufacturer
EXEC xp_readerrorlog 0, 1, "Manufacturer";
--#endregion
;
WITH sysInfo
AS (SELECT
cpu_count,
hyperthread_ratio,
sqlserver_start_time,
(SELECT
ISNULL(CAST(NULLIF(DATEDIFF(HOUR, sqlserver_start_time, GETDATE()) / 24, 0)AS VARCHAR) + ' days ', '') + RIGHT('0' + CAST(DATEDIFF(MINUTE, StartDateTime, GETDATE()) / 60 % 24 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(DATEDIFF(MINUTE, StartDateTime, GETDATE()) % 60 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(DATEDIFF(second, StartDateTime, GETDATE()) % 60 AS VARCHAR), 2)
FROM(SELECT
DATEDIFF(DAY, sqlserver_start_time, GETDATE())AS DayDiff)AS dd
CROSS APPLY(SELECT
CASE
WHEN DayDiff > 1 THEN DATEADD(DAY, DayDiff - 1, sqlserver_start_time)
ELSE sqlserver_start_time
END AS StartDateTime)AS b)AS SystemUpTime,
total_physical_memory_kb / 1024 AS TotalPhysicalMemoryMB,
available_physical_memory_kb / 1024 AS AvailPhysicalMemoryMB,
system_memory_state_desc AS SystemMemoryState,
physical_memory_in_use_kb / 1024 AS MemInUseMB,
memory_utilization_percentage AS [MemUtil%]
FROM sys.dm_os_sys_memory AS sm(NOLOCK)
CROSS JOIN sys.dm_os_process_memory AS pm(NOLOCK)
CROSS JOIN sys.dm_os_sys_info AS si(NOLOCK))
SELECT
CONVERT(VARCHAR(128), SERVERPROPERTY('Servername'))AS Servername,
CONVERT(VARCHAR(128), SERVERPROPERTY('MachineName'))AS MachineName,
CONVERT(VARCHAR(128), SERVERPROPERTY('InstanceName'))AS InstanceName,
@LoginMode AS AuthenticationMode,
@MSSQLServiceAccountName AS SQLServiceAccount,
@SQLAgentServiceAccountName AS SQLAgentServiceAccount,
(SELECT
CASE
WHEN ISNUMERIC(Data) = 1 THEN CAST(Data AS INT)
END
FROM @RegRead)AS Port,
CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))AS ComputerNamePhysicalNetBIOS,
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('IsClustered')) = 1 THEN 'Clustered'
WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'Not Clustered'
WHEN SERVERPROPERTY('IsClustered') = NULL THEN 'Error'
END AS IsClustered,
(SELECT TOP 1
Text
FROM #SystemManufacturer)AS SystemManufacturer,
@ProcessorInfo AS ProcessorInfo,
s.*,
CONVERT(INT, SERVERPROPERTY('ProcessId'))AS ProcessId,
CONVERT(INT, SERVERPROPERTY('IsSingleUser'))AS IsSingleUser,
CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion'))AS ProductVersion,
CONVERT(VARCHAR(128), SERVERPROPERTY('ProductLevel'))AS ProductLevel,
CONVERT(VARCHAR(128), SERVERPROPERTY('ResourceLastUpdateDateTime'))AS ResourceLastUpdateDateTime,
CONVERT(VARCHAR(128), SERVERPROPERTY('ResourceVersion'))AS ResourceVersion,
CASE
WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1 THEN 'Integrated security'
WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0 THEN 'Not Integrated security'
END AS IsIntegratedSecurityOnly,
CASE
WHEN SERVERPROPERTY('EngineEdition') = 1 THEN 'Personal Edition'
WHEN SERVERPROPERTY('EngineEdition') = 2 THEN 'Standard Edition'
WHEN SERVERPROPERTY('EngineEdition') = 3 THEN 'Enterprise Edition'
WHEN SERVERPROPERTY('EngineEdition') = 4 THEN 'Express Edition'
END AS EngineEdition,
CONVERT(VARCHAR(128), SERVERPROPERTY('LicenseType'))AS LicenseType,
CONVERT(VARCHAR(128), SERVERPROPERTY('NumLicenses'))AS NumLicenses,
CONVERT(VARCHAR(128), SERVERPROPERTY('BuildClrVersion'))AS BuildClrVersion,
CONVERT(VARCHAR(128), SERVERPROPERTY('Collation'))AS Collation,
CONVERT(VARCHAR(128), SERVERPROPERTY('CollationID'))AS CollationID,
CONVERT(VARCHAR(128), SERVERPROPERTY('ComparisonStyle'))AS ComparisonStyle,
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('EditionID')) = -1253826760 THEN 'Desktop Edition'
WHEN SERVERPROPERTY('EditionID') = -1592396055 THEN 'Express Edition'
WHEN SERVERPROPERTY('EditionID') = -1534726760 THEN 'Standard Edition'
WHEN SERVERPROPERTY('EditionID') = 1333529388 THEN 'Workgroup Edition'
WHEN SERVERPROPERTY('EditionID') = 1804890536 THEN 'Enterprise Edition'
WHEN SERVERPROPERTY('EditionID') = -323382091 THEN 'Personal Edition'
WHEN SERVERPROPERTY('EditionID') = -2117995310 THEN 'Developer Edition'
WHEN SERVERPROPERTY('EditionID') = 610778273 THEN 'Enterprise Evaluation Edition'
WHEN SERVERPROPERTY('EditionID') = 1044790755 THEN 'Windows Embedded SQL'
WHEN SERVERPROPERTY('EditionID') = 4161255391 THEN 'Express Edition with Advanced Services'
END AS ProductEdition,
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('IsFullTextInstalled')) = 1 THEN 'Full-text is installed'
WHEN SERVERPROPERTY('IsFullTextInstalled') = 0 THEN 'Full-text is not installed'
WHEN SERVERPROPERTY('IsFullTextInstalled') = NULL THEN 'Error'
END AS IsFullTextInstalled,
CONVERT(VARCHAR(128), SERVERPROPERTY('SqlCharSet'))AS SqlCharSet,
CONVERT(VARCHAR(128), SERVERPROPERTY('SqlCharSetName'))AS SqlCharSetName,
CONVERT(VARCHAR(128), SERVERPROPERTY('SqlSortOrder'))AS SqlSortOrderID,
CONVERT(VARCHAR(128), SERVERPROPERTY('SqlSortOrderName'))AS SqlSortOrderName
FROM sysInfo AS s
GO
No comments:
Post a Comment