Get SQL Server Database details using T-SQL

Here is a T-SQL script which gives you the details of all the databases in an SQL Server Instance.
This will be very useful when you are gathering SQL Server information from multiple servers

SET NOCOUNT ON
IF OBJECT_ID('tempdb..#DatabaseDetails') IS NOT NULL DROP TABLE #DatabaseDetails
CREATE TABLE #DatabaseDetails (
  DatabaseID int
, DatabaseName varchar(256)
, CreateDate datetime
, Collation varchar(256)
, ComparisonStyle int
, IsAnsiNullDefault bit
, IsAnsiNullsEnabled bit
, IsAnsiPaddingEnabled bit
, IsAnsiWarningsEnabled bit
, IsArithmeticAbortEnabled bit
, IsAutoClose bit
, IsAutoCreateStatistics bit
, IsAutoShrink bit
, IsAutoUpdateStatistics bit
, IsCloseCursorsOnCommitEnabled bit
, IsFulltextEnabled bit
, [IsInStandBy] bit
, IsLocalCursorsDefault bit
, IsMergePublished bit
, IsMergeSubscribed bit
, IsNullConcat bit
, IsNumericRoundAbortEnabled bit
, IsParameterizationForced bit
, [IsQuotedIdentifiersEnabled] bit
, IsPublished bit
, IsRecursiveTriggersEnabled bit
, IsSubscribed bit
, IsSyncWithBackup bit
, IsTornPageDetectionEnabled bit
, LCID int
, [Recovery] varchar(256)
, [SQLSortOrder] tinyint
, [Status] varchar(256)
, Updateability varchar(256)
, UserAccess varchar(256)
, [Version] int
, LastDatabaseBackup datetime
, LastIncremetalBackup datetime
, LastLogBackup datetime
, TotalLogSize bigint
, LogPercentUsed int
, [TotalDBSize_MB] bigint
, [cmptlevel] int
)

INSERT INTO #DatabaseDetails(
  DatabaseID
, [DatabaseName]
, [CreateDate]
, [Collation]
, [ComparisonStyle]
, [IsAnsiNullDefault]
, [IsAnsiNullsEnabled]
, [IsAnsiPaddingEnabled]
, [IsAnsiWarningsEnabled]
, [IsArithmeticAbortEnabled]
, [IsAutoClose]
, [IsAutoCreateStatistics]
, [IsAutoShrink]
, [IsAutoUpdateStatistics]
, [IsCloseCursorsOnCommitEnabled]
, [IsFulltextEnabled]
, [IsInStandBy]
, [IsLocalCursorsDefault]
, [IsMergePublished]
, [IsMergeSubscribed]
, [IsNullConcat]
, [IsNumericRoundAbortEnabled]
, [IsParameterizationForced]
, [IsQuotedIdentifiersEnabled]
, [IsPublished]
, [IsRecursiveTriggersEnabled]
, [IsSubscribed]
, [IsSyncWithBackup]
, [IsTornPageDetectionEnabled]
, [LCID]
, [Recovery]
, [SQLSortOrder]
, [Status]
, [Updateability]
, [UserAccess]
, [Version]
, [cmptlevel])
SELECT sd.dbid as 'DatabaseID'
 , sd.[name] as 'DatabaseName'
 , sd.crdate as 'CreateDate'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Collation') as varchar(256)) as 'Collation'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'ComparisonStyle') as varchar(256)) as 'ComparisonStyle'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiNullDefault') as bit) as 'IsAnsiNullDefault'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiNullsEnabled') as bit) as 'IsAnsiNullsEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiPaddingEnabled') as bit) as 'IsAnsiPaddingEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiWarningsEnabled') as bit) as 'IsAnsiWarningsEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsArithmeticAbortEnabled') as bit) as 'IsArithmeticAbortEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoClose') as bit) as 'IsAutoClose'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoCreateStatistics') as bit) as 'IsAutoCreateStatistics'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoShrink') as bit) as 'IsAutoShrink'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoUpdateStatistics') as bit) as 'IsAutoUpdateStatistics'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsCloseCursorsOnCommitEnabled') as bit) as 'IsCloseCursorsOnCommitEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsFulltextEnabled') as bit) as 'IsFulltextEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsInStandBy') as bit) as 'IsInStandBy'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsLocalCursorsDefault') as bit) as 'IsLocalCursorsDefault'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsMergePublished') as bit) as 'IsMergePublished'
 , CASE WHEN sd.category & 8 = 8 THEN 1 ELSE 0 end as 'IsMergeSubscribed'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsNullConcat') as bit) as 'IsNullConcat'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsNumericRoundAbortEnabled') as bit) as 'IsNumericRoundAbortEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsParameterizationForced') as bit) as 'IsParameterizationForced'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsQuotedIdentifiersEnabled') as bit) as 'IsQuotedIdentifiersEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsPublished') as bit) as 'IsPublished'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsRecursiveTriggersEnabled') as bit) as 'IsRecursiveTriggersEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsSubscribed') as bit) as 'IsSubscribed'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsSyncWithBackup') as bit) as 'IsSyncWithBackup'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsTornPageDetectionEnabled') as bit) as 'IsTornPageDetectionEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'LCID') as int) as 'LCID'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Recovery') as varchar(256)) as 'Recovery'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'SQLSortOrder') as tinyint) as 'SQLSortOrder'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Status') as varchar(256)) as 'Status'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Updateability') as varchar(256)) as 'Updateability'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'UserAccess') as varchar(256)) as 'UserAccess'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Version') as int) as 'Version'
 , cmptlevel
FROM master.dbo.sysdatabases sd 
ORDER BY sd.dbid



UPDATE dbd
SET   LastDatabaseBackup = fullbak.LastDatabaseBackup
 , LastIncremetalBackup = incbak.LastIncremetalBackup
 , LastLogBackup = logbak.LastLogBackup
FROM #DatabaseDetails dbd
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t1.backup_finish_date) as 'LastDatabaseBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t1  on t1.type = 'D' and t1.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) fullbak ON fullbak.dbid = dbd.DatabaseID
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t2.backup_finish_date) as 'LastIncremetalBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t2  on t2.type = 'I' and t2.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) incbak on incbak.dbid = dbd.DatabaseID
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t3.backup_finish_date) as 'LastLogBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t3  on t3.type = 'L' and t3.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) logbak on logbak.dbid = dbd.DatabaseID


IF OBJECT_ID('tempdb..#logspace') IS NOT NULL DROP TABLE #logspace
CREATE TABLE #logspace(
   DatabaseName varchar(256)
 , TotalLogSize decimal(20,4)
 , PercentUsed decimal(20,4)
 , [Status] varchar(50)
)
INSERT INTO #logspace
EXEC('DBCC sqlperf(logspace)')

UPDATE dbd
SET   TotalLogSize = ls.TotalLogSize
 , LogPercentUsed = Convert(int, ls.PercentUsed)
FROM #DatabaseDetails dbd
join #logspace ls ON dbd.DatabaseID = db_id(ls.DatabaseName)

EXEC master.dbo.sp_MSForEachDB 'update #DatabaseDetails
set TotalDBSize_MB = (select (sum([size]) * 8.0) / 1024.0 
FROM [?].[dbo].[sysfiles] )
where DatabaseID = db_id(''?'')'

SELECT DatabaseID
, DatabaseName
, [TotalDBSize_MB]
, TotalLogSize
, LogPercentUsed
, CreateDate
, [Status]
, LastDatabaseBackup
, LastIncremetalBackup
, LastLogBackup
, [Recovery]
, [Updateability]
, [UserAccess]
, [Collation]
, [ComparisonStyle]
, [LCID]
, [SQLSortOrder]
, [Version]
, [cmptlevel]
, [IsAutoUpdateStatistics]
, [IsAutoCreateStatistics]
, [IsInStandBy]
, [IsAutoShrink]
, [IsNullConcat]
, [IsFulltextEnabled]
, [IsPublished]
, [IsSubscribed]
, [IsMergePublished]
, [IsMergeSubscribed]
, [IsAnsiNullDefault]
, [IsAnsiNullsEnabled]
, [IsAnsiPaddingEnabled]
, [IsAnsiWarningsEnabled]
, [IsArithmeticAbortEnabled]
, [IsAutoClose]
, [IsCloseCursorsOnCommitEnabled]
, [IsLocalCursorsDefault]
, [IsNumericRoundAbortEnabled]
, [IsParameterizationForced]
, [IsQuotedIdentifiersEnabled]
, [IsRecursiveTriggersEnabled]
, [IsSyncWithBackup]
, [IsTornPageDetectionEnabled]
FROM #DatabaseDetails
ORDER BY DatabaseName
SET NOCOUNT OFF

What extra information can we get from a backup file?

Consider a situation where you got a alert saying the "disk is almost full" and you are browsing the disk to find out what can be removed. During this process you come across a orphaned backup file with just some name specified to that file and you do not know from which database this backup file is generated, when was it taken, is it a full or differential or log backup, etc, etc...
We all know that if we have the proper backup file(s), we can recover a database fully, but without the proper information we cannot recover. In this case, we do not know the information about the backup file that was found.

So how to proceed?
One way is to restore the backup and check,

1. But restoring of a backup taken in higher version is not allowed in lower version. For example, a backup taken in SQL Server 2008 cannot be restored in  SQL Server 2005.
2. Without a full backup we cannot start a new database restore and in this case we do not know what is the backup type.

So what next?

Another way is to check without restoring. For this, SQL Server provides additional commands that can be used with the backup file using which we can get extra information from the backup file.
These commands are

1. RESTORE HEADERONLY
2. RESTORE FILELISTONLY
3. RESTORE LABELONLY

RESTORE HEADERONLY - Returns the backup header information of the specified backup.

And the header information includes information about

Column name	Description for SQL Server backup sets
BackupName	Backup set name.
BackupDescription	Backup set description.
BackupType	Backup type: 1 = Database 2 = Transaction log 4 = File 5 = Differential database 6 = Differential file 7 = Partial 8 = Differential partial
ExpirationDate	Expiration date for the backup set.
Compressed	Whether the backup set is compressed using software-based compression: 0 = No 1 = Yes
Position	Position of the backup set in the volume (for use with the FILE = option).
DeviceType	Number corresponding to the device used for the backup operation. Disk: 2 = Logical 102 = Physical Tape: 5 = Logical 105 = Physical Virtual Device: 7 = Logical 107 = Physical
UserName	User name that performed the backup operation.
ServerName	Name of the server that wrote the backup set.
DatabaseName	Name of the database that was backed up.
DatabaseVersion	Version of the database from which the backup was created.
DatabaseCreationDate	Date and time the database was created.
BackupSize	Size of the backup, in bytes.
FirstLSN	Log sequence number of the first log record in the backup set.
LastLSN	Log sequence number of the next log record after the backup set.
CheckpointLSN	Log sequence number of the most recent checkpoint at the time the backup was created.
DatabaseBackupLSN	Log sequence number of the most recent full database backup.
BackupStartDate	Date and time that the backup operation began.
BackupFinishDate	Date and time that the backup operation finished.
SortOrder	Server sort order. This column is valid for database backups only. Provided for backward compatibility.
CodePage	Server code page or character set used by the server.
UnicodeLocaleId	Server Unicode locale ID configuration option used for Unicode character data sorting. Provided for backward compatibility.
UnicodeComparisonStyle	Server Unicode comparison style configuration option, which provides additional control over the sorting of Unicode data. Provided for backward compatibility.
CompatibilityLevel	Compatibility level setting of the database from which the backup was created.
SoftwareVendorId	Software vendor identification number. For SQL Server, this number is 4608 (or hexadecimal 0x1200).
SoftwareVersionMajor	Major version number of the server that created the backup set.
SoftwareVersionMinor	Minor version number of the server that created the backup set.
SoftwareVersionBuild	Build number of the server that created the backup set.
MachineName	Name of the computer that performed the backup operation.
Flags	Individual flags bit meanings if set to 1: 1 = Log backup contains bulk-logged operations. 2 = Snapshot backup. 4 = Database was read-only when backed up. 8 = Database was in single-user mode when backed up. 16 = Backup contains backup checksums. 32 = Database was damaged when backed up, but the backup operation was requested to continue despite errors. 64 = Tail log backup. 128 = Tail log backup with incomplete metadata. 256 = Tail log backup with NORECOVERY.
BindingID	Binding ID for the database
RecoveryForkID	ID for the ending recovery fork. This column corresponds to last_recovery_fork_guid in the backupset table.
Collation	Collation used by the database.
FamilyGUID	ID of the original database when created. This value stays the same when the database is restored.
HasBulkLoggedData	1 = Log backup containing bulk-logged operations.
IsSnapshot	1 = Snapshot backup.
IsReadOnly	1 = Database was read-only when backed up.
IsSingleUser	1 = Database was single-user when backed up.
HasBackupChecksums	1 = Backup contains backup checksums.
IsDamaged	1 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.
BeginsLogChain	1 = This is the first in a continuous chain of log backups. A log chain begins with the first log backup taken after the database is created or when it is switched from the Simple to the Full or Bulk-Logged Recovery Model.
HasIncompleteMetaData	1 = A tail-log backup with incomplete meta-data.
IsForceOffline	1 = Backup taken with NORECOVERY; the database was taken offline by backup.
IsCopyOnly	1 = A copy-only backup.
FirstRecoveryForkID	ID for the starting recovery fork. This column corresponds to first_recovery_fork_guid in the backupset table.
ForkPointLSN	If FirstRecoveryForkID is not equal to RecoveryForkID, this is the log sequence number of the fork point. Otherwise, this value is NULL.
RecoveryModel	Recovery model for the Database, one of: FULL BULK-LOGGED SIMPLE
DifferentialBaseLSN	For a single-based differential backup, the value equals the FirstLSN of the differential base; changes with LSNs greater than or equal to DifferentialBaseLSN are included in the differential. For non-differential backup types, the value is always NULL.
DifferentialBaseGUID	For a single-based differential backup, the value is the unique identifier of the differential base.
BackupTypeDescription	Backup type as string, one of: DATABASE TRANSACTION LOG FILE OR FILEGROUP DATABASE DIFFERENTIAL FILE DIFFERENTIAL PARTIAL PARTIAL DIFFERENTIAL
BackupSetGUID	Unique identification number of the backup set, by which it is identified on the media.
CompressedBackupSize	Byte count of the backup set. For uncompressed backups, this value is the same as BackupSize.

Example:

RESTORE FILELISTONLY - Returns the information about list of the database and log files contained in the backup. 

Column name	Description
LogicalName	Logical name of the file.
PhysicalName	Physical or operating-system name of the file.
Type	The type of file, one of: L = Microsoft SQL Server log file D = SQL Server data file F = Full Text Catalog
FileGroupName	Name of the filegroup that contains the file.
Size	Current size in bytes.
MaxSize	Maximum allowed size in bytes.
FileID	File identifier, unique within the database.
CreateLSN	Log sequence number at which the file was created.
DropLSN	The log sequence number at which the file was dropped. If the file has not been dropped, this value is NULL.
UniqueID	Globally unique identifier of the file.
ReadOnlyLSN	Log sequence number at which the filegroup containing the file changed from read-write to read-only (the most recent change).
ReadWriteLSN	Log sequence number at which the filegroup containing the file changed from read-only to read-write (the most recent change).
BackupSizeInBytes	Size of the backup for this file in bytes.
SourceBlockSize	Block size of the physical device containing the file in bytes (not the backup device).
FileGroupID	ID of the filegroup.
LogGroupGUID	NULL.
DifferentialBaseLSN	For differential backups, changes with log sequence numbers greater than or equal to DifferentialBaseLSN are included in the differential.
DifferentialBaseGUID	For differential backups, the unique identifier of the differential base. For other backup types, the value is NULL.
IsReadOnly	1 = The file is read-only.
IsPresent	1 = The file is present in the backup.

Example:

RESTORE LABELONLY - Returns the information about backup media of the given backup. 

Column name	Description
MediaName	Name of the media.
MediaSetId	Unique identification number of the media set.
FamilyCount	Number of media families in the media set.
FamilySequenceNumber	Sequence number of this family.
MediaFamilyId	Unique identification number for the media family.
MediaSequenceNumber	Sequence number of this media in the media family.
MediaLabelPresent	Whether the media description contains: 1 = Microsoft Tape Format media label 0 = Media description
MediaDescription	Media description, in free-form text, or the Tape Format media label.
SoftwareName	Name of the backup software that wrote the label.
SoftwareVendorId	Unique vendor identification number of the software vendor that wrote the backup.
MediaDate	Date and time the label was written.
Mirror_Count	Number of mirrors in the set (1-4).
IsCompressed	Whether the backup is compressed: 0 = not compressed 1 =compressed

Example:

Phases of Database recovery

From my previous post “What happens when a SQL Server instance is restarted?” we know what activities will be carried out when the SQL Server instance gets restart request.

Now, it’s time to understand what recovery phases the database will undergo.
The databases undergo recovery phases in two scenarios

1. When the SQL server  or service is restarted
2. When the database is being restored.

There are 3 Phases of Recovery and are based on the last checkpoint in the transaction log.

Recovery Phases - Drill Down

Recovery Phases - Graphical

What happens when a SQL Server instance is restarted?

Have you ever wondered or got curious to know what will happen or what are the activities carried out when an SQL Server instance get a restart request?

SQL Server instance will stop and then start again. Yes, this is obvious and there are lot more things that happen when a restart command is issued on an SQL Server instance.

With this post I am trying to list down the activities that happen during the restart of a SQL server instance, may be the sequence is not correct and the list might be incomplete. In that case, you can always correct me and complete the list. J

First of all, the service stops and before the service stops,

1. Checkpoint is issued on all databases
2. Check for the jobs that are running and stop them
3. Release the locks on database files to Operating System
4. Release the memory used by SQL Server instance
5. Flush the metadata collected for DMV’s and DMF’s
6. Record an event in default trace and event viewer regarding the SQL Server instance shutdown

During the starting of SQL Server service,

1. The service is authenticated by verifying the credentials provided in the logon account and the service is started.
2. Startup parameters (master database data file path, log file path and error log file path, etc… if any) are verified
3. The port on which SQL server is listening is opened.
4. Memory is allocated
5. Read master database metadata for information about user databases
6. Attach all the user database
7. Undergo database recovery phases (Analysis, redo and undo phases.)
8. Obtain lock on the database files
9. tempdb files are allocated based on the initial size settings and other setting like collation are copied from model database.
10. An entry to default trace is recorded about the start of SQL Server instance
11. All the events are recorded to SQL Server log file and event viewer
12. Accept connections to databases
13. Start the metadata collection for DMV’s and DMF’s
14. Recompile Stored Procedures

Order of Installing Service Pack on systems hosting Database Mirroring

If you have servers which hosts database mirroring and you want to install the service pack on those systems and you are trying to find out the order in which you need to do it then, here is the order in which you need to install service pack or a hotfix on the systems which hosts Database Mirroring.

1. Backup the principal
2. If you are using a witness, remove it from mirroring
3. Upgrade the mirror
4. Failover to mirror
5. Upgrade original principal/current mirror
6. If you wish to fail back to original principal continue on; otherwise, proceed to step 8
7. Failover to original principal
8. If you are using a witness, add it back into mirroring

For more information refer to the following article to know the entire process for upgrading both the principal and mirror servers.
http://technet.microsoft.com/en-us/library/bb497962(SQL.90).aspx

Get SQL Server details using T-SQL

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.

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