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
