SQL Server – Check Database Properties

The T-SQL query below can be used to check the properties of your databases.

USE master
GO
SELECT  name AS 'Database_Name' ,
        snapshot_isolation_state AS 'Allow Snapshot Isolation' ,
        is_ansi_null_default_on AS 'ANSI NULL Default' ,
        is_ansi_nulls_on AS 'ANSI NULLS Enabled' ,
        is_ansi_padding_on AS 'ANSI Paddings Enabled' ,
        is_ansi_warnings_on AS 'ANSI Warnings Enabled' ,
        is_arithabort_on AS 'Arithmetic Abort Enabled' ,
        is_auto_close_on AS 'Auto CLOSE' ,
        is_auto_create_stats_on AS 'Auto Create Statistics' ,
        is_auto_shrink_on AS 'Auto Shrink' ,
        is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' ,
        is_auto_update_stats_on AS 'Auto Update Statistics' ,
        is_cursor_close_on_commit_on AS 'Close Cursor on Commit Enabled' ,
        is_concat_null_yields_null_on AS 'Concatenate Null Yields Null' ,
        is_db_chaining_on AS 'Cross-Database Ownership Chaining Enabled' ,
        is_date_correlation_on AS 'Data Correlation Optimization Enabled' ,
        is_read_only AS 'Database Read-Only' ,
        is_local_cursor_default AS 'Default Cursor' ,
        is_encrypted AS 'Encryption Enabled' ,
        is_arithabort_on AS 'Numeric Round-Abort' ,
        page_verify_option_desc AS 'Page Verify' ,
        is_parameterization_forced AS 'Parameterization' ,
        is_quoted_identifier_on AS 'Quoted Identifiers Enabled' ,
        is_read_committed_snapshot_on AS 'Read Committed Snapshot' ,
        is_recursive_triggers_on AS 'Recursive Triggers Enabled' ,
        user_access_desc AS 'Restrict Access' ,
        is_broker_enabled AS 'Service Broker Enabled' ,
        is_trustworthy_on AS 'Trustworthy'
FROM    sys.databases ;
GO

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter