Here is a T-SQL script which gives you the details of a SQL Server.
This will be very useful when you are gathering SQL Server information from multiple servers and works for SQL Server 2005 and above.
This will be very useful when you are gathering SQL Server information from multiple servers and works for SQL Server 2005 and above.
CREATE TABLE #ServerDetails(ID int, Name sysname, Internal_Value int, Value nvarchar(512)) INSERT #ServerDetails EXEC master.dbo.xp_msver DECLARE @InstanceName nvarchar(50) DECLARE @value VARCHAR(100) DECLARE @RegKey_InstanceName nvarchar(500) DECLARE @RegKey nvarchar(500) DECLARE @AuditLevel int DECLARE @DataDirectory nvarchar(500) DECLARE @LogDirectory nvarchar(500) DECLARE @BackupDirectory nvarchar(500) SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'), 'MSSQLSERVER')) if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8 BEGIN SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' EXECUTE xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @RegKey_InstanceName, @value_name = @InstanceName, @value = @value OUTPUT SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\' EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@RegKey, @value_name='AuditLevel', @value=@AuditLevel OUTPUT EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@RegKey, @value_name='DefaultData', @value=@DataDirectory OUTPUT EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@RegKey, @value_name='DefaultLog', @value=@LogDirectory OUTPUT EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@RegKey, @value_name='BackupDirectory', @value=@BackupDirectory OUTPUT END SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name] ,SERVERPROPERTY('ServerName') AS [SQL Server Name] ,SERVERPROPERTY('InstanceName') AS [Instance Name] ,SERVERPROPERTY('Collation') AS [Server Collation] ,'Microsoft SQL Server ' + CAST(SERVERPROPERTY('Edition') AS varchar(250)) AS Edition ,SERVERPROPERTY('ProductLevel') AS [Product Level] ,(SELECT Value FROM #ServerDetails WHERE Name = N'Language') AS [Language] ,(SELECT Value FROM #ServerDetails WHERE Name = N'Platform') AS [Platform] ,(SELECT 'Microsoft Windows NT ' + Value from #ServerDetails where Name = N'WindowsVersion') AS [Operating System] ,(SELECT Internal_Value FROM #ServerDetails WHERE Name = N'ProcessorCount') AS [Processors] ,(SELECT CAST(Internal_Value AS varchar)+ ' (MB)' FROM #ServerDetails WHERE Name = N'PhysicalMemory') AS Memory , CASE WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'True' ELSE 'False' END AS IsClustered ,(SELECT value from sys.configurations where name='min server memory (MB)') AS 'Min Server Memory (MB)' ,(SELECT value from sys.configurations where name='max server memory (MB)') AS 'Max Server Memory (MB)' ,(SELECT CASE WHEN value=0 THEN 'True' ELSE 'False' END from sys.configurations where name='affinity mask') AS 'Automatically set processor affinity mask for all processor' ,(SELECT CASE WHEN value=0 THEN 'True' ELSE 'False' END from sys.configurations where name='affinity I/O mask') AS 'Automatically set I/O affinity mask for all processor' ,CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')= 1 THEN 'Windows Authentication Mode' WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')= 0 THEN 'SQL Server and Windows Authentication Mode' END AS [Server Authentication] ,CASE WHEN @AuditLevel = 0 THEN 'None' WHEN @AuditLevel = 1 THEN 'Successful Logins Only' WHEN @AuditLevel = 2 THEN 'Failed Logins Only' WHEN @AuditLevel = 3 THEN 'Both Failed and Successful Logins' END AS [Audit Level] ,(select CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations where name='remote access') AS 'Allow remote connections to this Server' ,(select CASE WHEN value = 0 THEN 'unlimited' ELSE value END from sys.configurations where name='user connections') AS 'Max number of concurrent Connections' ,(select CASE WHEN value = 0 THEN 'No Timeout' ELSE value END from sys.configurations where name='remote query timeout (s)') AS 'Query Timeout (s)' ,(select CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations where name='remote access') AS 'Allow Remote Connections to this server' ,@DataDirectory AS 'Default Data Directory' ,@LogDirectory AS 'Default Log Directory' ,@BackupDirectory AS 'Default Backup Directory' ,(SELECT value from sys.configurations WHERE name='max degree of parallelism') AS 'Max Degree of Parallelism' ,(SELECT value from sys.configurations WHERE name='remote login timeout (s)') AS 'Remote Login Timeout (s)' ,(SELECT CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations WHERE name='scan for startup procs') AS 'Scan for Startup Procs' DROP TABLE #ServerDetails
3 comments:
Nice, this gives a head start to the DBA who is newly introduced to the SQL Instance to understand its properties, data/log directories and its configurations.
Thank you so much...this is such an amazing script !!
Aivivu đại lý vé máy bay, tham khảo
vé máy bay đi Mỹ bao nhiêu
khi nào có chuyến bay từ mỹ về việt nam
vietnamairline từ nhật về việt nam
khi nào có chuyến bay từ đức về việt nam
giá vé máy bay từ Vancouver về việt nam
mua ve may bay tu han quoc ve viet nam
chuyen bay danh cho chuyen gia
Post a Comment