Hi All
Below Query is not created by me , I just copied from Internet. but its very useful to get details for your repository.
Refer for hardware details : How To Find Server Hardware details using SQL Query
/*********************************************************/
SET NOCOUNT ON;
DECLARE @SQLServerStartupMode [int]
,@SQLAgentStartupMode [int]
,@LoadID [int]
,@Position [int]
,@LoginMode [int]
,@SQLServerAuditLevel [int]
,@SQLServerStartupType [char](12)
,@SQLAgentStartupType [char](12)
,@SQLServerServiceAccount [varchar](64)
,@SQLAgentServiceAccount [varchar](64)
,@SQLServerRegistryKeyPath [varchar](256)
,@SQLAgentRegistryKeyPath [varchar](256)
,@InstanceName [nvarchar](128)
,@FullInstanceName [nvarchar](128)
,@SystemInstanceName [nvarchar](128)
,@ErrorLogDirectory [nvarchar](128)
,@Domain [nvarchar](64)
,@IPLine [nvarchar](256)
,@IpAddress [nvarchar](16)
,@ActiveNode [nvarchar](128)
,@AuthenticationMode [varchar](64)
,@PortNumber [varchar](8)
,@PageFile [varchar](124)
,@ClusterNodes [nvarchar](32)
,@BinariesPath [nvarchar](128)
,@RegistryKeyPath [nvarchar](256)
,@RegistryPath1 [nvarchar](256)
,@RegistryPath2 [nvarchar](256)
,@RegistryPath3 [nvarchar](256)
,@SQLServerInstallationLocation [nvarchar](512)
IF OBJECT_ID('[Tempdb].[dbo].[#_IPCONFIG_OUTPUT]') IS NOT NULL
DROP TABLE [dbo].[#_IPCONFIG_OUTPUT]
IF OBJECT_ID('[Tempdb].[dbo].[#_PAGE_FILE_DETAILS]') IS NOT NULL
DROP TABLE [dbo].[#_PAGE_FILE_DETAILS]
IF OBJECT_ID('[Tempdb].[dbo].[#_XPMSVER]') IS NOT NULL
DROP TABLE [dbo].[#_XPMSVER]
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = '##_SERVER_CONFIG_INFO'
AND [type] IN (N'U'))
DROP TABLE [dbo].[##_SERVER_CONFIG_INFO]
CREATE TABLE [dbo].[#_PAGE_FILE_DETAILS] ([data] [varchar](500))
CREATE TABLE [dbo].[#_IPCONFIG_OUTPUT] ([IPConfigCommandOutput] [nvarchar](256))
CREATE TABLE [dbo].[#_XPMSVER]([IDX] [int] NULL
,[C_NAME] [varchar](100) NULL
,[INT_VALUE] [float] NULL
,[C_VALUE] [varchar](128) NULL ) ON [PRIMARY]
CREATE TABLE [dbo].[##_SERVER_CONFIG_INFO](
[Domain] [nvarchar](64) NULL,
[SQLServerName] [varchar](64) NULL,
[InstanceName] [nvarchar](128) NULL,
[ComputerNamePhysicalNetBIOS] [nvarchar](128) NULL,
[IsClustered] [varchar](13) NULL,
[ClusterNodes] [nvarchar](32) NULL,
[ActiveNode] [nvarchar](128) NULL,
[HostIPAddress] [nvarchar](16) NULL,
[PortNumber] [varchar](8) NULL,
[IsIntegratedSecurityOnly] [varchar](64) NULL,
[AuditLevel] [varchar](38) NOT NULL,
[ProductVersion] [varchar](100) NULL,
[ProductLevel] [varchar](100) NULL,
[ResourceVersion] [varchar](100) NULL,
[ResourceLastUpdateDateTime] [varchar](100) NOT NULL,
[EngineEdition] [varchar](64) NULL,
[BuildClrVersion] [varchar](100) NOT NULL,
[Collation] [varchar](100) NULL,
[CollationID] [varchar](100) NULL,
[ComparisonStyle] [varchar](100) NULL,
[IsFullTextInstalled] [varchar](26) NULL,
[SQLCharset] [varchar](100) NOT NULL,
[SQLCharsetName] [varchar](100) NOT NULL,
[SQLSortOrderID] [varchar](100) NOT NULL,
[SQLSortOrderName] [varchar](100) NOT NULL,
[Platform] [varchar](128) NULL,
[FileDescription] [varchar](128) NULL,
[WindowsVersion] [varchar](128) NULL,
[ProcessorCount] [float] NULL,
[ProcessorType] [varchar](128) NULL,
[PhysicalMemory] [float] NULL,
[ServerPageFile] [varchar](124) NULL,
[SQLInstallationLocation] [nvarchar](512) NULL,
[BinariesPath] [nvarchar](128) NULL,
[ErrorLogsLocation] [nvarchar](128) NULL,
[MSSQLServerServiceStartupUser] [varchar](64) NULL,
[MSSQLAgentServiceStartupUser] [varchar](64) NULL,
[MSSQLServerServiceStartupType] [char](12) NULL,
[MSSQLAgentServiceStartupType] [char](12) NULL,
[InstanceLastStartDate] [datetime] NULL,
[LoadID] [int]) ON [PRIMARY]
------ Finding SQL Server and Agent Service Account Information ------
IF SERVERPROPERTY('InstanceName') IS NULL -- Default Instance
BEGIN --default instance
SET @SQLServerRegistryKeyPath='SYSTEM\CurrentControlSET\SERVICES\MSSQLSERVER'
SET @SQLAgentRegistryKeyPath='SYSTEM\CurrentControlSET\SERVICES\SQLSERVERAGENT'
END
ELSE
BEGIN --Named Instance
SET @SQLServerRegistryKeyPath = 'SYSTEM\CurrentControlSET\SERVICES\MSSQL$'
+ CAST (SERVERPROPERTY('InstanceName') AS [sysname])
SET @SQLAgentRegistryKeyPath = 'SYSTEM\CurrentControlSET\SERVICES\SQLAgent$'
+ CAST (SERVERPROPERTY('InstanceName') AS [sysname])
END
EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE'
,@SQLServerRegistryKeyPath
,@value_name = 'Start'
,@value = @SQLServerStartupMode OUTPUT
EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE'
,@SQLAgentRegistryKeyPath
,@value_name = 'Start'
,@value = @SQLAgentStartupMode OUTPUT
SET @SQLServerStartupType = (SELECT 'Start Up Mode' =
CASE
WHEN @SQLServerStartupMode = 2 THEN 'Automatic'
WHEN @SQLServerStartupMode = 3 THEN 'Manual'
WHEN @SQLServerStartupMode = 4 THEN 'Disabled'
END)
SET @SQLAgentStartupType = (SELECT 'Start Up Mode' =
CASE
WHEN @SQLAgentStartupMode = 2 THEN 'Automatic'
WHEN @SQLAgentStartupMode = 3 THEN 'Manual'
WHEN @SQLAgentStartupMode = 4 THEN 'Disabled'
END)
EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE'
,@SQLServerRegistryKeyPath
,@value_name = 'ObjectName'
,@value = @SQLServerServiceAccount OUTPUT
EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE'
,@SQLAgentRegistryKeyPath
,@value_name = 'ObjectName'
,@value = @SQLAgentServiceAccount OUTPUT
------ Reading registry keys for Binaries, Errorlogs location and Domain ------
SET @InstanceName = COALESCE (CONVERT([nvarchar](100)
, SERVERPROPERTY('InstanceName')), 'MSSQLSERVER');
IF @InstanceName != 'MSSQLSERVER'
BEGIN
SET @InstanceName = @InstanceName
END
SET @FullInstanceName = COALESCE (CONVERT([nvarchar](100)
, SERVERPROPERTY('InstanceName')), 'MSSQLSERVER');
IF @FullInstanceName != 'MSSQLSERVER'
BEGIN
SET @FullInstanceName = 'MSSQL$'+ @FullInstanceName
END
EXEC [master]..[xp_regread] N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'
,@InstanceName
,@SystemInstanceName OUTPUT;
SET @RegistryKeyPath = N'SYSTEM\CurrentControlSET\Services\'
+ @FullInstanceName;
SET @RegistryPath1 = N'Software\Microsoft\Microsoft SQL Server\'
+ @SystemInstanceName + '\MSSQLServer\Parameters';
SET @RegistryPath2 = N'Software\Microsoft\Microsoft SQL Server\'
+ @SystemInstanceName + '\MSSQLServer\supersocketnetlib\TCP\IP1';
SET @RegistryPath3 = N'SYSTEM\ControlSET001\Services\Tcpip\Parameters\';
IF @RegistryPath1 IS NULL
BEGIN
SET @InstanceName = COALESCE(CONVERT([nvarchar](100)
,SERVERPROPERTY('InstanceName')), 'MSSQLSERVER');
END
EXEC [master]..[xp_regread] N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'
,@InstanceName
,@SystemInstanceName OUTPUT;
EXEC [master]..[xp_regread] N'HKEY_LOCAL_MACHINE'
,@RegistryKeyPath
,@value_name = 'ImagePath'
,@value = @BinariesPath OUTPUT
EXEC [master]..[xp_regread] N'HKEY_LOCAL_MACHINE'
,@RegistryPath1
,@value_name = 'SQLArg1'
,@value = @ErrorLogDirectory OUTPUT
EXEC [master]..[xp_regread] N'HKEY_LOCAL_MACHINE'
,@RegistryPath3
,@value_name = 'Domain'
,@value = @Domain OUTPUT
SELECT @ClusterNodes = COALESCE(@ClusterNodes+', ' ,'') + [Nodename]
FROM [sys].[dm_os_cluster_nodes]
IF @ClusterNodes IS NULL
BEGIN
SET @ClusterNodes = 'Not Clustered'
END
SET @InstanceName = CONVERT([varchar](25), SERVERPROPERTY('InstanceName'))
EXEC [master]..[xp_instance_regread] N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'AuditLevel'
,@SQLServerAuditLevel OUTPUT
EXEC [master]..[xp_instance_regread] N'HKEY_LOCAL_MACHINE'
,N'SOFTWARE\Microsoft\MSSQLServer\Setup'
,N'SQLPath'
,@SQLServerInstallationLocation OUTPUT
------ Finding IP Address ------
INSERT #_IPCONFIG_OUTPUT EXEC [master]..[xp_cmdshell] 'ipconfig'
IF LEFT (CAST(SERVERPROPERTY('ProductVersion')AS [sysname]), 5) = '10.50'
BEGIN
SELECT @IPLine = [IPConfigCommandOutput]
FROM #_IPCONFIG_OUTPUT
WHERE UPPER ([IPConfigCommandOutput]) LIKE '%IPv4 Address%'
IF (ISNULL(@IPLine,'***') != '***')
BEGIN
SET @Position = CharIndex (':', @IPLine, 1);
SET @IPAddress = RTRIM(LTRIM(SUBSTRING(@IPLine
,@Position + 1 ,LEN(@IPLine) - @Position)))
END
END
ELSE
BEGIN
SELECT @IPLine = [IPConfigCommandOutput]
FROM #_IPCONFIG_OUTPUT
WHERE UPPER ([IPConfigCommandOutput]) LIKE '%IP Address%'
IF (ISNULL(@IPLine,'***') != '***')
BEGIN
SET @Position = CharIndex (':', @IPLine, 1);
SET @IPAddress = RTRIM(LTRIM(SUBSTRING(@IPLine
,@Position + 1,LEN(@IPLine) - @Position)))
END
END
------ Finding Port Information ------
IF @InstanceName IS NULL
BEGIN
SET @RegistryKeyPath = 'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'
END
ELSE
BEGIN
SET @RegistryKeyPath = 'Software\Microsoft\Microsoft SQL Server\'
+ @InstanceName + '\MSSQLServer\SuperSocketNetLib\Tcp\'
END
EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE'
,@RegistryKeyPath
,@value_name = 'tcpPort'
,@value = @PortNumber OUTPUT -- Port Number
------ Finding Authentication Mode ------
EXEC [master]..[xp_instance_regread] N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,@value_name = N'LoginMode'
,@value = @LoginMode OUTPUT
SET @AuthenticationMode = (SELECT 'AuTHENtication Mode' =
CASE
WHEN @LoginMode = 1 THEN 'Windows Authentication'
WHEN @LoginMode = 2 THEN 'Mixed Mode Authentication'
END )
------ Finding Active Node ------
EXEC [master]..[xp_regread] @rootkey = 'HKEY_LOCAL_MACHINE'
,@RegistryKeyPath = 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName'
,@value_name = 'ComputerName'
,@value = @ActiveNode OUTPUT
INSERT INTO [#_PAGE_FILE_DETAILS]
EXEC [master]..[xp_cmdshell] 'wmic pagefile list /format:list'
SELECT @PageFile = RTRIM(LTRIM([data]))
FROM #_PAGE_FILE_DETAILS
WHERE [data] LIKE 'AllocatedBaseSize%'
INSERT INTO [#_XPMSVER]
EXEC( 'master.dbo.xp_msver')
SELECT
UPPER(@Domain) AS [Domain]
,CONVERT([varchar](64),SERVERPROPERTY('ServerName')) AS [SQLServerName]
,@FullInstanceName AS [InstanceName]
,@ActiveNode AS [ComputerNamePhysicalNetBIOS]
,(CASE
WHEN CONVERT([varchar](100),SERVERPROPERTY('IsClustered')) = 1 THEN 'Clustered'
WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'Not Clustered'
WHEN SERVERPROPERTY('IsClustered') = NULL THEN 'Error'
END) AS [IsClustered]
,@ClusterNodes AS [ClusterNodes]
,@ActiveNode AS [ActiveNode]
,@IPAddress AS [HostIPAddress]
,@PortNumber AS [PortNumber]
,@AuthenticationMode AS [IsIntegratedSecurityOnly]
,(CASE
WHEN @SQLServerAuditLevel = 0 THEN 'None.'
WHEN @SQLServerAuditLevel = 1 THEN 'Successful Logins Only'
WHEN @SQLServerAuditLevel = 2 THEN 'Failed Logins Only'
WHEN @SQLServerAuditLevel = 3 THEN 'Both Failed and Successful Logins Only'
ELSE 'N/A' END) AS [AuditLevel]
,CONVERT([varchar](100),SERVERPROPERTY('ProductVersion')) AS [ProductVersion]
,CONVERT([varchar](100),SERVERPROPERTY('ProductLevel')) AS [ProductLevel]
,ISNULL(CONVERT([varchar](100),SERVERPROPERTY('ResourceVersion'))
,CONVERT([varchar](100),SERVERPROPERTY('ProductVersion'))) AS [ResourceVersion]
,ISNULL(CONVERT([varchar](100),SERVERPROPERTY('ResourceLastUpdateDateTime'))
,'Information Not Available') AS [ResourceLastUpdateDateTime]
,CAST (SERVERPROPERTY('Edition') as [varchar](64)) AS [EngineEdition]
,ISNULL(CONVERT([varchar](100),SERVERPROPERTY('BuildClrVersion')), 'NOT Applicable') AS [BuildClrVersion]
,CONVERT([varchar](100),SERVERPROPERTY('Collation')) AS [Collation]
,CONVERT([varchar](100),SERVERPROPERTY('CollationID')) AS [CollationID]
,CONVERT([varchar](100),SERVERPROPERTY('ComparisonStyle')) AS [ComparisonStyle]
,(CASE
WHEN CONVERT([varchar](100),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]
,ISNULL (CONVERT([varchar](100), SERVERPROPERTY('SqlCharSet')), 'No Information') AS [SQLCharset]
,ISNULL (CONVERT([varchar](100), SERVERPROPERTY('SqlCharSetName')), 'No Information') AS [SQLCharsetName]
,ISNULL (CONVERT([varchar](100), SERVERPROPERTY('SqlSortOrder')), 'No Information') AS [SQLSortOrderID]
,ISNULL (CONVERT([varchar](100), SERVERPROPERTY('SqlSortOrderName')), 'No Information') AS [SQLSortOrderName]
,(SELECT C_VALUE from [#_XPMSVER] where [C_NAME] = 'Platform') as [Platform]
,(SELECT C_VALUE from [#_XPMSVER] where [C_NAME] = 'FileDescription' ) as [FileDescription]
,(SELECT C_VALUE from [#_XPMSVER] where [C_NAME] = 'WindowsVersion') as [WindowsVersion]
,(SELECT INT_VALUE from [#_XPMSVER] where [C_NAME] = 'ProcessorCount') as [ProcessorCount]
,(SELECT ISNULL(C_VALUE,CAST (INT_VALUE AS VARCHAR(9))) from #_XPMSVER where [C_NAME] = 'ProcessorType') as [ProcessorType]
,(SELECT INT_VALUE from [#_XPMSVER] where [C_NAME] = 'PhysicalMemory') as [PhysicalMemory]
,@PageFile AS [ServerPageFile]
,@SQLServerInstallationLocation AS [SQLInstallationLocation]
,@BinariesPath AS [BinariesPath]
,@ErrorLogDirectory AS [ErrorLogsLocation]
,@SQLServerServiceAccount AS [MSSQLServerServiceStartupUser]
,@SQLAgentServiceAccount AS [MSSQLAgentServiceStartupUser]
,@SQLServerStartupType AS [MSSQLServerServiceStartupType]
,@SQLAgentStartupType AS [MSSQLAgentServiceStartupType]
,(SELECT [login_time] FROM [master]..[sysprocesses] WHERE [spid] = 1) AS [InstanceLastStartDate]
-- Dropping temporary table
IF OBJECT_ID('[Tempdb].[dbo].[#_IPCONFIG_OUTPUT]') IS NOT NULL
DROP TABLE [dbo].[#_IPCONFIG_OUTPUT]
IF OBJECT_ID('[Tempdb].[dbo].[#_PAGE_FILE_DETAILS]') IS NOT NULL
DROP TABLE [dbo].[#_PAGE_FILE_DETAILS]
IF OBJECT_ID('[Tempdb].[dbo].[#_XPMSVER]') IS NOT NULL
DROP TABLE [dbo].[#_XPMSVER]
GO
/*********************************************************/
Refer for hardware details : How To Find Server Hardware details using SQL Query
No comments:
Post a Comment