USE [master]
GO
IF OBJECT_ID('dbo.sp_SysInfo') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_SysInfo AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_SysInfo
@ReturnInfo BIT = 0,
@InFo VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON
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)
--#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 @RegRead
(Value,
DATA)
EXEC [master].[dbo].[xp_regread]
'HKEY_LOCAL_MACHINE',
@RegLoc,
'tcpPort' ;
--#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
IF OBJECT_ID('tempdb..#sp_SysInfo') IS NOT NULL
DROP TABLE #sp_SysInfo ;
WITH sysInfo
AS (SELECT cpu_count,
hyperthread_ratio,
sqlserver_start_time,
Util.dbo.GetDateTimeDifference(sqlserver_start_time, GETDATE()) 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,
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
INTO #sp_SysInfo
FROM sysInfo s
IF @ReturnInfo = 1
BEGIN
DECLARE @NSQL NVARCHAR(MAX)
SELECT @NSQL = 'SELECT
@InFo = ' + Util.dbo.StringConcat('''' + ColumnName + ': '' + ' + ColumnValue, ' + ''
'' + ') + '
FROM #sp_SysInfo'
FROM (SELECT TOP 999999
c.name AS ColumnName,
C2 AS ColumnValue
FROM tempdb.sys.columns c (NOLOCK)
INNER JOIN tempdb.sys.types t ON t.user_type_id = c.user_type_id
CROSS APPLY (SELECT CASE WHEN t.name LIKE '%char%' THEN QUOTENAME(c.name)
ELSE 'CAST(' + QUOTENAME(c.name) + ' AS VARCHAR)'
END AS C1) d
CROSS APPLY (SELECT CASE WHEN c.is_nullable = 1 THEN 'ISNULL(' + C1 + ', '''')' ELSE C1 END AS C2) e
WHERE c.OBJECT_ID = OBJECT_ID('tempdb..#sp_SysInfo')
ORDER BY c.name) k
EXEC sys.sp_executesql
@NSQL,
N'@InFo VARCHAR(MAX) OUTPUT',
@InFo OUTPUT
END
ELSE
SELECT *
FROM #sp_SysInfo
GO
EXEC sys.sp_ms_marksystemobject
sp_SysInfo
GO