Skip to content Skip to sidebar Skip to footer
Home / Sql Server / Sql Server 2014 / Tsql

Script Out Server Properties And Database Properties

Post a Comment
MSSQL 2014 If possible, can someone provide the tsql to extract the settings from the pages of Server Properties and Database Properties? Thanks

Solution 1:

You are looking for DATABASEPROPERTY and SERVERPROPERTY.

Just list them in a SELECT statement like:

SELECT 
 [Service_Name]                 = @@SERVICENAME
,[Server Name]                  = SERVERPROPERTY('ServerName')
,[Physical_Net_BIOS_Name]       = SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
,[Edition]                      = SERVERPROPERTY('Edition')
,[Product_Version]              = SERVERPROPERTY('ProductVersion')
,[Product_Update_Reference]     = SERVERPROPERTY('ProductUpdateReference')
,[Resource_Last_Update_Date]    = SERVERPROPERTY('ResourceLastUpdateDateTime')
,[Process_ID]                   = SERVERPROPERTY('ProcessID')
,[Collation]                    = SERVERPROPERTY('Collation')
,[CLR_Version]                  = SERVERPROPERTY('BuildClrVersion')
,[Is_Clustered]                 = SERVERPROPERTY('IsClustered') 
,[Is_FullText_Installed]        = SERVERPROPERTY('IsFullTextInstalled') 
,[Is_Integrated_Security_Only]  = SERVERPROPERTY('IsIntegratedSecurityOnly')
,[File_Stream_Configured_Level] = SERVERPROPERTY('FilestreamConfiguredLevel')
,[Is_HA_DR_Enabled]             = SERVERPROPERTY('IsHadrEnabled') 
,[HA_DR_Manager_Status]         = SERVERPROPERTY('HadrManagerStatus')
,[Default_Data_Path]            = SERVERPROPERTY('InstanceDefaultDataPath')
,[Default_Log_Path]             = SERVERPROPERTY('InstanceDefaultLogPath')

And add/delete any property you need.

Solution 2:

I stumbled across your old post. I wrote this for DATABASEPROPERTYEX since I need to do the same:

DECLARE@DB SYSNAME ='adventureworks'SELECT'Collation'as Property, DATABASEPROPERTYEX (@DB, 'Collation') asValueUNIONSELECT'ComparisonStyle'as Property, DATABASEPROPERTYEX (@DB, 'ComparisonStyle') asValueUNIONSELECT'Edition'as Property, DATABASEPROPERTYEX (@DB, 'Edition') asValueUNIONSELECT'IsAnsiNullDefault'as Property, DATABASEPROPERTYEX (@DB, 'IsAnsiNullDefault') asValueUNIONSELECT'IsAnsiNullsEnabled'as Property, DATABASEPROPERTYEX (@DB, 'IsAnsiNullsEnabled') asValueUNIONSELECT'IsAnsiPaddingEnabled'as Property, DATABASEPROPERTYEX (@DB, 'IsAnsiPaddingEnabled') asValueUNIONSELECT'IsAnsiWarningsEnabled'as Property, DATABASEPROPERTYEX (@DB, 'IsAnsiWarningsEnabled') asValueUNIONSELECT'IsArithmeticAbortEnabled'as Property, DATABASEPROPERTYEX (@DB, 'IsArithmeticAbortEnabled') asValueUNIONSELECT'IsAutoClose'as Property, DATABASEPROPERTYEX (@DB, 'IsAutoClose') asValueUNIONSELECT'IsAutoCreateStatistics'as Property, DATABASEPROPERTYEX (@DB, 'IsAutoCreateStatistics') asValueUNIONSELECT'IsAutoCreateStatisticsIncremental'as Property, DATABASEPROPERTYEX (@DB, 'IsAutoCreateStatisticsIncremental') asValueUNIONSELECT'IsAutoShrink'as Property, DATABASEPROPERTYEX (@DB, 'IsAutoShrink') asValueUNIONSELECT'IsAutoUpdateStatistics'as Property, DATABASEPROPERTYEX (@DB, 'IsAutoUpdateStatistics') asValueUNIONSELECT'IsClone'as Property, DATABASEPROPERTYEX (@DB, 'IsClone') asValueUNIONSELECT'IsCloseCursorsOnCommitEnabled'as Property, DATABASEPROPERTYEX (@DB, 'IsCloseCursorsOnCommitEnabled') asValueUNIONSELECT'IsFulltextEnabled'as Property, DATABASEPROPERTYEX (@DB, 'IsFulltextEnabled') asValueUNIONSELECT'IsInStandBy'as Property, DATABASEPROPERTYEX (@DB, 'IsInStandBy') asValueUNIONSELECT'IsLocalCursorsDefault'as Property, DATABASEPROPERTYEX (@DB, 'IsLocalCursorsDefault') asValueUNIONSELECT'IsMemoryOptimizedElevateToSnapshotEnabled'as Property, DATABASEPROPERTYEX (@DB, 'IsMemoryOptimizedElevateToSnapshotEnabled') asValueUNIONSELECT'IsMergePublished'as Property, DATABASEPROPERTYEX (@DB, 'IsMergePublished') asValueUNIONSELECT'IsNullConcat'as Property, DATABASEPROPERTYEX (@DB, 'IsNullConcat') asValueUNIONSELECT'IsNumericRoundAbortEnabled'as Property, DATABASEPROPERTYEX (@DB, 'IsNumericRoundAbortEnabled') asValueUNIONSELECT'IsParameterizationForced'as Property, DATABASEPROPERTYEX (@DB, 'IsParameterizationForced') asValueUNIONSELECT'IsQuotedIdentifiersEnabled'as Property, DATABASEPROPERTYEX (@DB, 'IsQuotedIdentifiersEnabled') asValueUNIONSELECT'IsPublished'as Property, DATABASEPROPERTYEX (@DB, 'IsPublished') asValueUNIONSELECT'IsRecursiveTriggersEnabled'as Property, DATABASEPROPERTYEX (@DB, 'IsRecursiveTriggersEnabled') asValueUNIONSELECT'IsSubscribed'as Property, DATABASEPROPERTYEX (@DB, 'IsSubscribed') asValueUNIONSELECT'IsSyncWithBackup'as Property, DATABASEPROPERTYEX (@DB, 'IsSyncWithBackup') asValueUNIONSELECT'IsTornPageDetectionEnabled'as Property, DATABASEPROPERTYEX (@DB, 'IsTornPageDetectionEnabled') asValueUNIONSELECT'IsVerifiedClone'as Property, DATABASEPROPERTYEX (@DB, 'IsVerifiedClone') asValueUNIONSELECT'IsXTPSupported'as Property, DATABASEPROPERTYEX (@DB, 'IsXTPSupported') asValueUNIONSELECT'LastGoodCheckDbTime'as Property, DATABASEPROPERTYEX (@DB, 'LastGoodCheckDbTime') asValueUNIONSELECT'LCID'as Property, DATABASEPROPERTYEX (@DB, 'LCID') asValueUNIONSELECT'MaxSizeInBytes'as Property, DATABASEPROPERTYEX (@DB, 'MaxSizeInBytes') asValueUNIONSELECT'Recovery'as Property, DATABASEPROPERTYEX (@DB, 'Recovery') asValueUNIONSELECT'ServiceObjective'as Property, DATABASEPROPERTYEX (@DB, 'ServiceObjective') asValueUNIONSELECT'ServiceObjectiveId'as Property, DATABASEPROPERTYEX (@DB, 'ServiceObjectiveId') asValueUNIONSELECT'SQLSortOrder'as Property, DATABASEPROPERTYEX (@DB, 'SQLSortOrder') asValueUNIONSELECT'Status'as Property, DATABASEPROPERTYEX (@DB, 'Status') asValueUNIONSELECT'Updateability'as Property, DATABASEPROPERTYEX (@DB, 'Updateability') asValueUNIONSELECT'UserAccess'as Property, DATABASEPROPERTYEX (@DB, 'UserAccess') asValueUNIONSELECT'Version'as Property, DATABASEPROPERTYEX (@DB, 'Version') asValue

Solution 3: 

declare@dbnamevarchar(max)='adventureworks2016ctp3'select'name'as feature, cast(name as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'database_id', cast(database_id as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'source_database_id', cast(source_database_id as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'owner_sid', cast(user_name(owner_sid) as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'create_date', cast(create_date as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'compatibility_level', cast(compatibility_level as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'collation_name', cast(collation_name as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'user_access', cast(user_access as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'user_access_desc', cast(user_access_desc as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_read_only', cast(is_read_only as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_auto_close_on', cast(is_auto_close_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_auto_shrink_on', cast(is_auto_shrink_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'state', cast(state as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'state_desc', cast(state_desc as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_in_standby', cast(is_in_standby as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_cleanly_shutdown', cast(is_cleanly_shutdown as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_supplemental_logging_enabled', cast(is_supplemental_logging_enabled as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'snapshot_isolation_state', cast(snapshot_isolation_state as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'snapshot_isolation_state_desc', cast(snapshot_isolation_state_desc as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_read_committed_snapshot_on', cast(is_read_committed_snapshot_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'recovery_model', cast(recovery_model as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'recovery_model_desc', cast(recovery_model_desc as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'page_verify_option', cast(page_verify_option as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'page_verify_option_desc', cast(page_verify_option_desc as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_auto_create_stats_on', cast(is_auto_create_stats_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_auto_create_stats_incremental_on', cast(is_auto_create_stats_incremental_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_auto_update_stats_on', cast(is_auto_update_stats_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_auto_update_stats_async_on', cast(is_auto_update_stats_async_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_ansi_null_default_on', cast(is_ansi_null_default_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_ansi_nulls_on', cast(is_ansi_nulls_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_ansi_padding_on', cast(is_ansi_padding_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_ansi_warnings_on', cast(is_ansi_warnings_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_arithabort_on', cast(is_arithabort_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_concat_null_yields_null_on', cast(is_concat_null_yields_null_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_numeric_roundabort_on', cast(is_numeric_roundabort_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_quoted_identifier_on', cast(is_quoted_identifier_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_recursive_triggers_on', cast(is_recursive_triggers_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_cursor_close_on_commit_on', cast(is_cursor_close_on_commit_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_local_cursor_default', cast(is_local_cursor_default as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_fulltext_enabled', cast(is_fulltext_enabled as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_trustworthy_on', cast(is_trustworthy_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_db_chaining_on', cast(is_db_chaining_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_parameterization_forced', cast(is_parameterization_forced as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_master_key_encrypted_by_server', cast(is_master_key_encrypted_by_server as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_query_store_on', cast(is_query_store_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_published', cast(is_published as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_subscribed', cast(is_subscribed as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_merge_published', cast(is_merge_published as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_distributor', cast(is_distributor as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_sync_with_backup', cast(is_sync_with_backup as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'service_broker_guid', cast(service_broker_guid as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_broker_enabled', cast(is_broker_enabled as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'log_reuse_wait', cast(log_reuse_wait as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'log_reuse_wait_desc', cast(log_reuse_wait_desc as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_date_correlation_on', cast(is_date_correlation_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_cdc_enabled', cast(is_cdc_enabled as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_encrypted', cast(is_encrypted as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_honor_broker_priority_on', cast(is_honor_broker_priority_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'replica_id', cast(replica_id as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'group_database_id', cast(group_database_id as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'resource_pool_id', cast(resource_pool_id as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'default_language_lcid', cast(default_language_lcid as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'default_language_name', cast(default_language_name as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'default_fulltext_language_lcid', cast(default_fulltext_language_lcid as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'default_fulltext_language_name', cast(default_fulltext_language_name as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_nested_triggers_on', cast(is_nested_triggers_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_transform_noise_words_on', cast(is_transform_noise_words_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'two_digit_year_cutoff', cast(two_digit_year_cutoff as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'containment', cast(containment as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'containment_desc', cast(containment_desc as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'target_recovery_time_in_seconds', cast(target_recovery_time_in_seconds as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'delayed_durability', cast(delayed_durability as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'delayed_durability_desc', cast(delayed_durability_desc as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_memory_optimized_elevate_to_snapshot_on', cast(is_memory_optimized_elevate_to_snapshot_on asvarchar(60)) as " value" from sys.databases where name=@dbnameunionselect'is_federation_member', cast(is_federation_member as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_remote_data_archive_enabled', cast(is_remote_data_archive_enabled as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_mixed_page_allocation_on', cast(is_mixed_page_allocation_on as sql_variant) as " value" from sys.databases where name=@dbnameunionselect'is_temporal_history_retention_enabled', cast(is_temporal_history_retention_enabled asvarchar(70)) as " value" from sys.databases where name=@dbname

Post a Comment for "Script Out Server Properties And Database Properties"