MsSQL - Verify Properties

/*

This will check the proprties on a given Database and also the server

Created By	: Steve Ling 2022/04/07

*/

--Change the DB name here

DECLARE @DATABASE NVARCHAR(50) = 'SHOP'

/* 

No changes below this line

*/

---- Simple Version Query

--SELECT SERVERPROPERTY( 'productversion' ) AS "Product Version" , 

-- SERVERPROPERTY ( 'productlevel' ) AS "Product Level" , 

-- SERVERPROPERTY ( 'edition' ) AS "Edition" 

-- Full Properties Query

 SELECT 

 SERVERPROPERTY( 'ComputerNamePhysicalNetBIOS' ) AS 'Current Failover Machine' , 

 SERVERPROPERTY( 'MachineName' ) AS 'Main Machine Name' , 

 SERVERPROPERTY( 'ServerName' ) AS 'Server\Instance Name' , 

 SERVERPROPERTY( 'InstanceName' ) AS 'Instance Name' , 

 SERVERPROPERTY( 'ProcessID' ) AS 'Instance ProcessID' , 

 CASE SUBSTRING ( CONVERT (VARCHAR(50), SERVERPROPERTY( 'productversion' )), 1, 4) 

 WHEN '8.00' THEN 'SQL2000' 

 WHEN '9.00' THEN 'SQL2005' 

 WHEN '10.0' THEN 'SQL2008' 

 WHEN '10.5' THEN 'SQL2008 R2' 

 WHEN '11.0' THEN 'SQL2012' 

 WHEN '12.0' THEN 'SQL2014' 

 ELSE 'Undetermined' END AS 'SQL Version' , 

 SERVERPROPERTY( 'ProductVersion' ) AS 'Product Version' , 

 SERVERPROPERTY( 'ProductLevel' ) AS 'Product Level' , 

 SERVERPROPERTY( 'Edition' ) AS 'Edition' , 

 CASE SERVERPROPERTY( 'EngineEdition' ) 

 WHEN 1 THEN 'Person / Desktop' 

 WHEN 2 THEN 'Standard' 

 WHEN 3 THEN 'Enterprise' 

 WHEN 4 THEN 'Express' 

 WHEN 5 THEN 'SQL Database' 

 ELSE 'Unknown' END AS EngineEdition, 

 CASE SERVERPROPERTY( 'IsIntegratedSecurityOnly' ) 

 WHEN 1 THEN 'Window Authentication' 

 WHEN 0 THEN 'Windows and SQL Authenication' 

 ELSE 'Unknown' END AS 'Security Mode' , 

 SERVERPROPERTY( 'Collation' ) AS 'Collation' , 

 SERVERPROPERTY( 'BuildClrVersion' ) AS 'CLR Version' , 

 CASE SERVERPROPERTY( 'IsFullTextInstalled' ) 

 WHEN 0 THEN 'Installed' 

 WHEN 1 THEN 'Not Installed' 

 ELSE 'Unknown' END AS 'Full Text Indexing' , 

 CASE SERVERPROPERTY( 'IsHadrEnabled' ) 

 WHEN 0 THEN 'Disabled' 

 WHEN 1 THEN 'Enabled' 

 ELSE 'Unknown' END AS 'AlwaysOn Availability Groups' , 

 CASE SERVERPROPERTY( 'HadrManagerStatus' ) 

 WHEN 0 THEN 'Not Started / PENDING' 

 WHEN 1 THEN 'Start/Running' 

 WHEN 2 THEN 'Not Started / FAILED' 

 ELSE 'Unknown' END AS 'AlwaysOn Availability Groups Manager' , 

 

 CASE SERVERPROPERTY( 'IsClustered' ) 

 WHEN 0 THEN 'Clustered' 

 WHEN 1 THEN 'Not Clustered' 

 ELSE 'Unknown' END AS 'Failover Cluster' , 

 CASE SERVERPROPERTY( 'IsSingleUser' ) 

 WHEN 0 THEN 'Not in Single User Mode' 

 WHEN 1 THEN 'In Single User Mode' 

 ELSE 'Unknown' END AS 'Single User Mode' ; 

DECLARE @DB SYSNAME = @DATABASE

SELECT 'Collation' as Property, DATABASEPROPERTYEX (@DB, 'Collation') as Value UNION

SELECT 'ComparisonStyle' as Property, DATABASEPROPERTYEX (@DB, 'ComparisonStyle') as Value UNION

SELECT 'Edition' as Property, DATABASEPROPERTYEX (@DB, 'Edition') as Value UNION

SELECT 'IsAnsiNullDefault' as Property, DATABASEPROPERTYEX (@DB, 'IsAnsiNullDefault') as Value UNION

SELECT 'IsAnsiNullsEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsAnsiNullsEnabled') as Value UNION

SELECT 'IsAnsiPaddingEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsAnsiPaddingEnabled') as Value UNION

SELECT 'IsAnsiWarningsEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsAnsiWarningsEnabled') as Value UNION

SELECT 'IsArithmeticAbortEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsArithmeticAbortEnabled') as Value UNION

SELECT 'IsAutoClose' as Property, DATABASEPROPERTYEX (@DB, 'IsAutoClose') as Value UNION

SELECT 'IsAutoCreateStatistics' as Property, DATABASEPROPERTYEX (@DB, 'IsAutoCreateStatistics') as Value UNION

SELECT 'IsAutoCreateStatisticsIncremental' as Property, DATABASEPROPERTYEX (@DB, 'IsAutoCreateStatisticsIncremental') as Value UNION

SELECT 'IsAutoShrink' as Property, DATABASEPROPERTYEX (@DB, 'IsAutoShrink') as Value UNION

SELECT 'IsAutoUpdateStatistics' as Property, DATABASEPROPERTYEX (@DB, 'IsAutoUpdateStatistics') as Value UNION

SELECT 'IsClone' as Property, DATABASEPROPERTYEX (@DB, 'IsClone') as Value UNION

SELECT 'IsCloseCursorsOnCommitEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsCloseCursorsOnCommitEnabled') as Value UNION

SELECT 'IsFulltextEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsFulltextEnabled') as Value UNION

SELECT 'IsInStandBy' as Property, DATABASEPROPERTYEX (@DB, 'IsInStandBy') as Value UNION

SELECT 'IsLocalCursorsDefault' as Property, DATABASEPROPERTYEX (@DB, 'IsLocalCursorsDefault') as Value UNION

SELECT 'IsMemoryOptimizedElevateToSnapshotEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsMemoryOptimizedElevateToSnapshotEnabled') as Value UNION

SELECT 'IsMergePublished' as Property, DATABASEPROPERTYEX (@DB, 'IsMergePublished') as Value UNION

SELECT 'IsNullConcat' as Property, DATABASEPROPERTYEX (@DB, 'IsNullConcat') as Value UNION

SELECT 'IsNumericRoundAbortEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsNumericRoundAbortEnabled') as Value UNION

SELECT 'IsParameterizationForced' as Property, DATABASEPROPERTYEX (@DB, 'IsParameterizationForced') as Value UNION

SELECT 'IsQuotedIdentifiersEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsQuotedIdentifiersEnabled') as Value UNION

SELECT 'IsPublished' as Property, DATABASEPROPERTYEX (@DB, 'IsPublished') as Value UNION

SELECT 'IsRecursiveTriggersEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsRecursiveTriggersEnabled') as Value UNION

SELECT 'IsSubscribed' as Property, DATABASEPROPERTYEX (@DB, 'IsSubscribed') as Value UNION

SELECT 'IsSyncWithBackup' as Property, DATABASEPROPERTYEX (@DB, 'IsSyncWithBackup') as Value UNION

SELECT 'IsTornPageDetectionEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsTornPageDetectionEnabled') as Value UNION

SELECT 'IsVerifiedClone' as Property, DATABASEPROPERTYEX (@DB, 'IsVerifiedClone') as Value UNION

SELECT 'IsXTPSupported' as Property, DATABASEPROPERTYEX (@DB, 'IsXTPSupported') as Value UNION

SELECT 'LastGoodCheckDbTime' as Property, DATABASEPROPERTYEX (@DB, 'LastGoodCheckDbTime') as Value UNION

SELECT 'LCID' as Property, DATABASEPROPERTYEX (@DB, 'LCID') as Value UNION

SELECT 'MaxSizeInBytes' as Property, DATABASEPROPERTYEX (@DB, 'MaxSizeInBytes') as Value UNION

SELECT 'Recovery' as Property, DATABASEPROPERTYEX (@DB, 'Recovery') as Value UNION

SELECT 'ServiceObjective' as Property, DATABASEPROPERTYEX (@DB, 'ServiceObjective') as Value UNION

SELECT 'ServiceObjectiveId' as Property, DATABASEPROPERTYEX (@DB, 'ServiceObjectiveId') as Value UNION

SELECT 'SQLSortOrder' as Property, DATABASEPROPERTYEX (@DB, 'SQLSortOrder') as Value UNION

SELECT 'Status' as Property, DATABASEPROPERTYEX (@DB, 'Status') as Value UNION

SELECT 'Updateability' as Property, DATABASEPROPERTYEX (@DB, 'Updateability') as Value UNION

SELECT 'UserAccess' as Property, DATABASEPROPERTYEX (@DB, 'UserAccess') as Value UNION

SELECT 'Version' as Property, DATABASEPROPERTYEX (@DB, 'Version') as Value

DECLARE @props TABLE (propertyname sysname PRIMARY KEY)

INSERT INTO @props(propertyname)

SELECT 'BuildClrVersion'

UNION

SELECT 'Collation'

UNION

SELECT 'CollationID'

UNION

SELECT 'ComparisonStyle'

UNION

SELECT 'ComputerNamePhysicalNetBIOS'

UNION

SELECT 'Edition'

UNION

SELECT 'EditionID'

UNION

SELECT 'EngineEdition'

UNION

SELECT 'InstanceName'

UNION

SELECT 'IsClustered'

UNION

SELECT 'IsFullTextInstalled'

UNION

SELECT 'IsIntegratedSecurityOnly'

UNION

SELECT 'IsSingleUser'

UNION

SELECT 'LCID'

UNION

SELECT 'LicenseType'

UNION

SELECT 'MachineName'

UNION

SELECT 'NumLicenses'

UNION

SELECT 'ProcessID'

UNION

SELECT 'ProductVersion'

UNION

SELECT 'ProductLevel'

UNION

SELECT 'ResourceLastUpdateDateTime'

UNION

SELECT 'ResourceVersion'

UNION

SELECT 'ServerName'

UNION

SELECT 'SqlCharSet'

UNION

SELECT 'SqlCharSetName'

UNION

SELECT 'SqlSortOrder'

UNION

SELECT 'SqlSortOrderName'

UNION

SELECT 'FilestreamShareName'

UNION

SELECT 'FilestreamConfiguredLevel'

UNION

SELECT 'FilestreamEffectiveLevel'

 

SELECT propertyname, SERVERPROPERTY(propertyname) FROM @props