Create an overview from your SQL server

Sometimes need a script to create an overview from your server so i collected the followings queries

-SQL Server name
-SQL Server Authentication Mode
-SQL Server Login Auditing
-C2 audit status
-SQL Cluster info
-List all SQL instances
-SQL Instance  properties
-Databases info
-DB LIST and RECOVERY MODEL
-DB backup info
-SQL log space usage
-SQL login list
-SQL password policy settings
-Cross database ownership chaining status
-List all job and start time
-SQL Error Log Iterations
-SQL server Network config
-SQL Server Directories

-SQL server Memory Configuration

PRINT N'_________________________________________________________________________';
PRINT N'|  SQL Configuration Checker 1.2v  Created 2013.04.18.                                               |';
PRINT N'_________________________________________________________________________';
PRINT N'_______________________________SQL Server name___________________________';
SELECT @@SERVERNAME

PRINT N'_______________________________SQL Server Authentication Mode_______________';
DECLARE @AuthenticationMode INT  
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer',   
N'LoginMode', @AuthenticationMode OUTPUT  

SELECT CASE @AuthenticationMode    
WHEN 1 THEN 'Windows Authentication'   
WHEN 2 THEN 'Windows and SQL Server Authentication'   
ELSE 'Unknown'  
END as [Authentication Mode] 

GO

PRINT N'_______________________________Login Auditing_____________________________';

DECLARE @AuditLevel INT  
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer',   
N'AuditLevel', @AuditLevel OUTPUT  

  IF @AuditLevel = 0 PRINT 'None'
  IF @AuditLevel = 1 PRINT 'Successful Logins Only'
  IF @AuditLevel = 2 PRINT 'Failed Logins Only'
  IF @AuditLevel = 3 PRINT 'Both Failed and Successful Logins' 

GO

PRINT N'_______________________________C2 audit mode______________________________';
DECLARE @AuditMode sql_variant;
SELECT @AuditMode = 0;
SELECT @AuditMode = value
FROM sys.configurations
WHERE name='c2 audit mode'
IF @AuditMode = 0 PRINT 'NOK! - C2 Audit Mode Disabled'
IF @AuditMode = 1 PRINT 'C2 Audit Mode Enabled'
GO


PRINT N'_______________________________LIST CLUSTER INFO__________________________';
DECLARE @notCluster VARCHAR(100);
SELECT @notCluster = 'Stand-alone system.';

IF(SELECT COUNT(NodeName) FROM fn_virtualservernodes()) = 0
SELECT @notCluster
ELSE
BEGIN
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName]
SELECT * FROM fn_virtualservernodes()
SELECT * FROM sys.dm_os_cluster_nodes 
SELECT * FROM fn_servershareddrives() 
SELECT * FROM sys.dm_io_cluster_shared_drives
END 

GO

PRINT N'_______________________________LIST ALL INSTANCES_________________________';
DECLARE @GetInstances TABLE
( Value nvarchar(100),
 InstanceNames nvarchar(100),
 Data nvarchar(100))

Insert into @GetInstances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'

Select InstanceNames from @GetInstances;

GO

PRINT N'_______________________________INSTANCE PROPERTIES______________________';

SELECT SERVERPROPERTY ('InstanceName');
SELECT 
          SERVERPROPERTY('MachineName') as Host,
          SERVERPROPERTY('InstanceName') as Instance,
          SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
          SERVERPROPERTY('ProductLevel') as ProductLevel, /* RTM or SP1 etc*/
          Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else
      'STANDALONE' end as ServerType,
          @@VERSION as VersionNumber
          
GO



PRINT N'_______________________________DB INFO____________________________________';         
declare @GB    float
declare @MB    float
declare @KB    float
declare @B     float

-- ***** Assign Variable Values *****
set @GB   =  (cast(8192 as float) / cast(1073741824 as float))
set @MB   =  (cast(8192 as float) / cast(1048576 as float))
set @KB   =  (cast(8192 as float) / cast(1024 as float))
set @B    =  (cast(8192 as float))

select db_name(database_id) as 'Database Name',      
       database_id as 'Database ID',
      (case type
            when 0 then 'Data File'
            when 1 then 'Log File'
            when 2 then 'Filestream File'
            when 3 then 'Other – N/A'
            when 4 then 'Full Text Catalog File'
            else ''
       end) as 'File Type',
       name as 'Logical File Name',
       physical_name as 'Physical File Name and Location',
     ((convert(float,size) * CONVERT(float,8)) / CONVERT(float,1024)) as 'Declared Size in MB',
      (case when (cast(size as float) * @GB) > (cast(1 as float))
            then (convert(varchar(16),(cast(size as float) * @GB)) +  ' GB')
            else (case when (cast(size as float) * @MB) > (cast(1 as float))
                       then (convert(varchar(16),(cast(size as float) * @MB)) + ' MB')
                       else (case when (cast(size as float) * @KB) > (cast(1 as float))
                                  then (convert(varchar(16),(cast(size as float) * @KB)) + ' KB')
                                  else ((convert(varchar(16),(cast(size as float) * @B))) + ' Bytes')
                             end)                 
                  end)        
       end) as 'Actual Size',       
      (case when is_percent_growth = 0                  
            then (case when (cast(growth as float) * @GB) > (cast(1 as float))
                       then (convert(varchar(16),(cast(growth as float) * @GB)) +  ' GB')
                       else (case when (cast(growth as float) * @MB) > (cast(1 as float))
                                  then  (convert(varchar(16),(cast(growth as float) * @MB)) + ' MB')
                                  else (case when (cast(growth as float) * @KB) > (cast(1 as float))
                                             then (convert(varchar(16),(cast(growth as float) * @KB)) + ' KB')
                                             else ((convert(varchar(16),(cast(growth as float) * @B))) + ' Bytes')
                                        end)
                             end)
                  end)
            when is_percent_growth = 1
            then (case growth
                       when 0 then 'N/A'
                       else (convert(varchar(16),(cast(growth as float))) + ' %')
                  end)
            else ''
       end) as 'Growth Method'
 from  master.sys.master_files
 order by database_id, type, size desc

GO


PRINT N'_______________________________DB LIST and RECOVERY MODEL_____________________';

SELECT name,compatibility_level,recovery_model_desc,state_desc  FROM sys.databases

GO
PRINT N'_______________________________DB LAST BACKUP_________________________________';         
SELECT db.name, 
case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100), 
MAX(b.backup_finish_date)) end AS last_backup_finish_date
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D'
WHERE db.database_id NOT IN (2) 
GROUP BY db.name
ORDER BY 2 DESC

GO

PRINT N'_______________________________LOG SPACE USAGE _______________________________';         

DBCC SQLPERF(LOGSPACE);

GO

PRINT N'_______________________________USER LIST _______________________________________';         
SELECT name,  
"Sysadmin" =
CASE
WHEN sysadmin = 0 THEN 'NO'
WHEN sysadmin = 1 THEN 'YES'
ELSE 'UNKNOW'
END
,"Securityadmin" =
CASE
WHEN securityadmin = 0 THEN 'NO'
WHEN securityadmin = 1 THEN 'YES'
ELSE 'UNKNOW'
END


FROM master.dbo.syslogins

GO


PRINT N'_________________________Password policy _______________________________________';         

SELECT 
    [name]
,"Enforce password policy" =
CASE
WHEN is_policy_checked = 0 THEN 'NO'
WHEN is_policy_checked = 1 THEN 'YES'
ELSE 'UNKNOW'
END
  , "Enforce password expiration" =
CASE
WHEN is_expiration_checked = 0 THEN 'NO'
WHEN is_expiration_checked = 1 THEN 'YES'
ELSE 'UNKNOW'
END

FROM sys.sql_logins;  

GO


PRINT N'_________________________Cross database ownership chaining _______________________';         

SELECT 
    [name]
,"Cross database ownership chaining" =
CASE
WHEN is_db_chaining_on = 0 THEN 'DISABLED'
WHEN is_db_chaining_on = 1 THEN 'ENABLED'
ELSE 'UNKNOW'
END
from sys.databases;

GO



PRINT N'_______________________________JOB LIST and START TIME _________________________';         

SELECT msdb.dbo.sysjobs.name AS Jobname, CAST(msdb.dbo.sysschedules.active_start_time / 10000 AS VARCHAR(10))  
+ ':' + RIGHT('00' + CAST(msdb.dbo.sysschedules.active_start_time % 10000 / 100 AS VARCHAR(10)), 2) AS Active_Start_Time 
FROM msdb.dbo.sysjobs 
INNER JOIN msdb.dbo.sysjobschedules ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id 
INNER JOIN msdb.dbo.sysschedules ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id  

GO

PRINT N'_______________________________Error Log Iterations_________________________';         

USE [master] 
GO 
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs' 
GO 
PRINT N'_____________________________________________________ _________________________';         
GO

PRINT N'_______________________________Network config_________________________';         DECLARE 
@TCPEnabled VARCHAR(3),
@ListenAll VARCHAR(3),
@TCPPort VARCHAR(30),
@DynPort VARCHAR(30);

DECLARE @_NetworkConfig TABLE(Variable VARCHAR(30), Value VARCHAR(30));

SELECT 
@TCPEnabled = 
CASE
WHEN value_name = 'Enabled' AND value_data = 0 THEN 'NO'
ELSE 'YES'
END 
FROM 
[master].[sys].[dm_server_registry]
WHERE 
registry_key LIKE '%SuperSocketNetLib\TCP' AND value_name = 'Enabled';

SELECT 
@ListenAll = 
CASE
WHEN value_name = 'ListenOnAllIPs' AND value_data = 0 THEN 'NO'
ELSE 'YES'
END 
FROM 
[master].[sys].[dm_server_registry]
WHERE 
registry_key LIKE '%SuperSocketNetLib\TCP' AND value_name = 'Enabled'; 

SELECT @TCPPort =
CASE
WHEN [value_data] = '' THEN 'Not Configured'
ELSE CONVERT(VARCHAR(30), [value_data])
END
FROM 
[master].[sys].[dm_server_registry]
WHERE 
registry_key LIKE '%SuperSocketNetLib\TCP\IPALL' AND value_name = 'TcpPort'; 

SELECT @DynPort =
CASE
WHEN [value_data] = '' OR value_data = '0' THEN 'Not Configured'
ELSE CONVERT(VARCHAR(30), [value_data])
END
FROM 
[master].[sys].[dm_server_registry]
WHERE 
registry_key LIKE '%SuperSocketNetLib\TCP\IPALL' AND value_name = 'TcpDynamicPorts'; 

INSERT INTO @_NetworkConfig VALUES('TCP Enabled', @TCPEnabled);
INSERT INTO @_NetworkConfig VALUES('Listen on All IPs', @ListenAll);
INSERT INTO @_NetworkConfig VALUES('Static TCP Port', @TCPPort);
INSERT INTO @_NetworkConfig VALUES('Dynamic TCP Port', @DynPort);

SELECT Variable AS [Configuration], Value FROM @_NetworkConfig; 

PRINT N'_______________________________SQL Server Directories_________________________';            
GO

USE [master] 

DECLARE @_Statistics TABLE(Value VARCHAR(30), Data VARCHAR(1024));
DECLARE @Value VARCHAR(1024);

EXEC master.dbo.xp_instance_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\Setup', 
N'SQLBinRoot', @Value OUTPUT;

INSERT INTO @_Statistics VALUES('SQL Binaries', @Value);

EXEC master.dbo.xp_instance_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\Setup', 
N'SQLDataRoot', @Value OUTPUT;

INSERT INTO @_Statistics VALUES('SQL Data Root', @Value);

EXEC master.dbo.xp_instance_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\Setup', 
N'SQLProgramDir', @Value OUTPUT;

INSERT INTO @_Statistics VALUES('SQL Program Directory', @Value);

EXEC master.dbo.xp_instance_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'BackupDirectory', @Value OUTPUT;

INSERT INTO @_Statistics VALUES('SQL Backup Directory', @Value);

EXEC master.dbo.xp_instance_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\SQLServerAgent', 
N'ErrorLogFile', @Value OUTPUT;

INSERT INTO @_Statistics VALUES('SQL Error Log File', @Value);

EXEC master.dbo.xp_instance_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\SQLServerAgent', 
N'WorkingDirectory', @Value OUTPUT;

INSERT INTO @_Statistics VALUES('SQL Agent Working Directory', @Value);

SELECT * FROM @_Statistics; 

PRINT N'_______________________________Memory Configuration_________________________';            


DECLARE @_ConfigTable TABLE
(
Variable VARCHAR(30),
Value VARCHAR(16)
);

DECLARE 
@intVal INT,
@memoryVal VARCHAR(16),
@returnData VARCHAR(30);

SELECT @returnData = cpu_count FROM [master].[sys].[dm_os_sys_info];
INSERT INTO @_ConfigTable VALUES('CPU Count', CONVERT(VARCHAR(16), @returnData));
SELECT @returnData = hyperthread_ratio FROM [master].[sys].[dm_os_sys_info]; 
INSERT INTO @_ConfigTable VALUES('CPU Cores (HT)',  CONVERT(VARCHAR(16), @returnData));

--SQL 2005, SQL2008 VS SQL2012
IF (SELECT COUNT(*) FROM sysobjects WHERE id IN (SELECT id FROM syscolumns WHERE name = 'physical_memory_in_bytes')) = 1
BEGIN
EXEC sp_executesql N'SELECT @retVal = FLOOR([physical_memory_in_bytes] / 1024) FROM [master].[sys].[dm_os_sys_info];',N'@retVal INT OUTPUT', @retVal = @intVal OUTPUT; 
SET @memoryVal = CONVERT(VARCHAR(16), FLOOR(@intVal / 1024)) + ' MB';
END
ELSE
BEGIN
EXEC sp_executesql N'SELECT @retVal = FLOOR([physical_memory_kb] / 1024) FROM [master].[sys].[dm_os_sys_info];',N'@retVal INT OUTPUT', @retVal = @intVal OUTPUT; 
SET @memoryVal = CONVERT(VARCHAR(16), @intVal) + ' MB';
END;

INSERT INTO @_ConfigTable VALUES('Total OS Memory', @memoryVal);




SELECT @intVal = CONVERT(INT, value) FROM [master].[sys].[configurations] WHERE NAME = 'Max server memory (MB)';
IF @intVal > 1024 SET @memoryVal = CONVERT(VARCHAR(16), FLOOR(@intVal / 1024)) + ' GB';
ELSE
BEGIN
IF @intVal > 1048576 
SET @memoryVal = CONVERT(VARCHAR(16), @intVal) + ' TB';
ELSE
SET @memoryVal = CONVERT(VARCHAR(16), @intVal) + ' MB';
END;
INSERT INTO @_ConfigTable VALUES('SQL Current Memory', @memoryVal);

SELECT @intVal = CONVERT(INT, minimum) FROM [master].[sys].[configurations] WHERE NAME = 'Max server memory (MB)';
IF @intVal > 1024 SET @memoryVal = CONVERT(VARCHAR(16), FLOOR(@intVal / 1024)) + ' GB';
ELSE
BEGIN
IF @intVal > 1048576 
SET @memoryVal = CONVERT(VARCHAR(16), @intVal) + ' TB';
ELSE
SET @memoryVal = CONVERT(VARCHAR(16), @intVal) + ' MB';
END;
INSERT INTO @_ConfigTable VALUES('SQL Minimum Memory', @memoryVal);

SELECT @intVal = CONVERT(INT, maximum) FROM [master].[sys].[configurations] WHERE NAME = 'Max server memory (MB)';
IF @intVal > 1024 AND @intVal < 1048576 SET @memoryVal = CONVERT(VARCHAR(16), FLOOR(@intVal / 1024)) + ' GB';
ELSE
BEGIN
IF @intVal > 1048576 
SET @memoryVal = CONVERT(VARCHAR(16), FLOOR(@intVal / 1048576)) + ' TB';
ELSE
SET @memoryVal = CONVERT(VARCHAR(16), @intVal) + ' MB';
END;
INSERT INTO @_ConfigTable VALUES('SQL Maximum Memory', @memoryVal);

SELECT * FROM @_ConfigTable; 

List default database for SQL logins

If you want list all SQL login with default DB you can use this query:

select name,
       loginname,
       dbname as DefaultDB,
       DATABASEPROPERTYEX(dbname, 'Status') as DBStatus
from sys.syslogins
order by DBstatus;

Result

Welcome

Welcome to my Blog

I'm an experienced IT professional with over 13 years in commercial sectors. ( IBM mainframe - 6 year - Windows server -4 year - SQL server 3 year) I am a MCITP: Database Administrator 2008 and Server Administrator on Windows Server 2008 . I have expertise in MSSQL database and Windows server deployment, disaster recovery, backup/restore , health check, user management and operation When I am not working, I try to spend as much time as possible with my bride and family