This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects. You can find a list of all of the deprecated views in the introduction post.

The compatibility view sysdatabases returns rows for each database on the SQL Server instance. The columns in the view provide information on the state, configuration, and the availability of the database. The list of database includes both the system and the user database on the instance.

The replacement catalog view is sys.databases. This view provides the same information in a format that, as we’ll discuss, makes it easier to obtain information on the databases. Similar to sysdatabases, the catalog view returns information on both system and user databases.

Status Columns

Similar to other compatibility views, sysdatabases has a Status column that contains information on the state and properties for each database stored in bits. Besides the one column, there are two other columns, Status2 and Category, that provide additional information about the databases.

The Status column contains settings for the database and descriptors about the status of the database. These fields are:

• 0×1: autoclose
• 0×4: select into/bulkcopy
• 0×8: trunc. log on chkpt
• 0×10: torn page detection
• 0×20: loading
• 0×40: pre recovery
• 0×80: recovering
• 0×100: not recovered
• 0×200: offline
• 0×400: read only
• 0×800: dbo use only
• 0×1000: single user
• 0×8000: emergency mode
• 0×400000: autoshrink
• 0×40000000: cleanly shutdown

Along with these descriptions, there are those in the Status2 column; which are:

• 0×4000: ANSI null default
• 0×10000: concatenation of null yields null
• 0×20000: recursive triggers
• 0×100000: default to local cursor
• 0×800000: quoted identifier
• 0×2000000: cursor close on commit
• 0×4000000: ANSI nulls
• 0×10000000: ANSI warnings
• 0×20000000: full text enabled

The last set of bit information is presented with Category column. The information in this column pertains to how the database is configured for replication. The values in this column are:

• 0×1: Published for snapshot or transactional replication
• 0×2: Subscribed to a snapshot or transactional publication
• 0×4: Published for merge replication
• 0×8: Subscribed to a merge publication
• 0×10: Functions as a distribution database

One thing to consider with three bit columns on the compatibility view is that queries against sysdatabases have the potential for complexity. As we’ll see shortly, this is definitely the case.

Query via Sys.Databases

Before looking at how to query the compatibility view, let’s first look at what is available in the replacement catalog view sys.databases. This contains all of the information available in the bit columns of sysdatabases plus some some other information. The one key column that is not included in sys.databases is the version information; which can be retrieved from the DATABASEPROPERTYEX function. A typical query against the catalog view is similar to the code in listing 1. The one key thing to remember is that, besides the one function, all of the data from sys.databases is provided directly from the catalog view.

Listing 1 – Query for sys.database
SELECT name 
    ,database_id 
    ,source_database_id 
    ,owner_sid 
    ,create_date 
    ,compatibility_level 
    ,collation_name 
    ,user_access 
    ,user_access_desc 
    ,is_read_only 
    ,is_auto_close_on 
    ,is_auto_shrink_on 
    ,state 
    ,state_desc 
    ,is_in_standby 
    ,is_cleanly_shutdown 
    ,is_supplemental_logging_enabled 
    ,snapshot_isolation_state 
    ,snapshot_isolation_state_desc 
    ,is_read_committed_snapshot_on 
    ,recovery_model 
    ,recovery_model_desc 
    ,page_verify_option 
    ,page_verify_option_desc 
    ,is_auto_create_stats_on 
    ,is_auto_update_stats_on 
    ,is_auto_update_stats_async_on 
    ,is_ansi_null_default_on 
    ,is_ansi_nulls_on 
    ,is_ansi_padding_on 
    ,is_ansi_warnings_on 
    ,is_arithabort_on 
    ,is_concat_null_yields_null_on 
    ,is_numeric_roundabort_on 
    ,is_quoted_identifier_on 
    ,is_recursive_triggers_on 
    ,is_cursor_close_on_commit_on 
    ,is_local_cursor_default 
    ,is_fulltext_enabled 
    ,is_trustworthy_on 
    ,is_db_chaining_on 
    ,is_parameterization_forced 
    ,is_master_key_encrypted_by_server 
    ,is_published 
    ,is_subscribed 
    ,is_merge_published 
    ,is_distributor 
    ,is_sync_with_backup 
    ,service_broker_guid 
    ,is_broker_enabled 
    ,log_reuse_wait 
    ,log_reuse_wait_desc 
    ,is_date_correlation_on 
    ,is_cdc_enabled 
    ,is_encrypted 
    ,is_honor_broker_priority_on 
    ,replica_id 
    ,group_database_id 
    ,default_language_lcid 
    ,default_language_name 
    ,default_fulltext_language_lcid 
    ,default_fulltext_language_name 
    ,is_nested_triggers_on 
    ,is_transform_noise_words_on 
    ,two_digit_year_cutoff 
    ,containment 
    ,containment_desc 
    ,target_recovery_time_in_seconds 
    ,DATABASEPROPERTYEX(d.name, 'version') AS version 
FROM sys.databases d

Query Via sysdatabases

As already alluded to, when it comes to querying sysdatabases, the query can get a bit more complicated. There are two reasons for this. First, many of the properties are stored in bit columns which need to be translated to be useful. Second, another group of properties aren’t available in the view and require extensive use of DATABASEPROPERTYEX in order to retrieve the information. Of the twelve columns in sysdatabases, only six of them are immediately useful and two more have no current value for the user. To retrieve the same information as is presented through sys.databases, the query against sysdatabases would required the use of the code in listing 2.

Listing 1 – Query for syscomments 
SELECT d.name 
,d.dbid 
,d.sid 
,d.crdate 
,d.cmptlevel 
,DATABASEPROPERTYEX(d.name, 'Collation') AS collation_name 
,DATABASEPROPERTYEX(d.name, 'UserAccess') AS user_access 
,CONVERT(INT,d.status & 0x1000) / 4096 AS is_state_single_user 
,CONVERT(INT,d.status & 0x800) / 2048 AS is_state_dbo_use_only 
,CONVERT(INT,d.status & 0x400) / 1024 AS is_read_only 
,CONVERT(INT,d.status & 0x1) / 1 AS is_auto_close_on 
,CONVERT(INT,d.status & 0x400000) / 4194304 AS is_auto_shrink_on 
,DATABASEPROPERTYEX(d.name, 'Status') AS state_desc 
,CONVERT(INT,d.status & 0x20) / 32 AS is_state_loading 
,CONVERT(INT,d.status & 0x40) / 64 AS is_state_pre_recovery 
,CONVERT(INT,d.status & 0x80) / 128 AS is_state_recovering 
,CONVERT(INT,d.status & 0x100) / 256 AS is_state_not_recovered 
,CONVERT(INT,d.status & 0x200) / 512 AS is_state_offline 
,CONVERT(INT,d.status & 0x8000) / 32768 AS emergency_mode 
,DATABASEPROPERTYEX(d.name, 'IsInStandBy') AS is_in_standby 
,CONVERT(INT,d.status & 0x40000000) / 1073741824 AS is_cleanly_shutdown 
,DATABASEPROPERTYEX(d.name, 'Recovery') AS recovery_model_desc 
,CONVERT(INT,d.status & 0x4) / 4 AS select_into_bulkcopy -- bulklogged model 
,CONVERT(INT,d.status & 0x8) / 8 AS [trunc. log on chkpt] -- simple model 
,CONVERT(INT,d.status & 0x10) / 16 AS torn_page_detection 
,DATABASEPROPERTYEX(d.name, 'IsAutoCreateStatistics') AS is_auto_create_stats_on 
,DATABASEPROPERTYEX(d.name, 'IsAutoUpdateStatistics') AS is_auto_update_stats_on 
,CONVERT(INT,d.status2 & 0x4000) / 16384 AS is_ansi_null_default_on 
,CONVERT(INT,d.status2 & 0x4000000) / 67108864 AS is_ansi_nulls_on 
,DATABASEPROPERTYEX(d.name, 'IsAnsiPaddingEnabled') AS is_ansi_padding_on 
,CONVERT(INT,d.status2 & 0x10000000) / 268435456 AS is_ansi_warnings_on 
,DATABASEPROPERTYEX(d.name, 'IsArithmeticAbortEnabled') AS is_arithabort_on 
,CONVERT(INT,d.status2 & 0x10000) / 65536 AS is_concat_null_yields_null_on 
,DATABASEPROPERTYEX(d.name, 'IsNumericRoundAbortEnabled') AS is_numeric_roundabort_on 
,CONVERT(INT,d.status2 & 0x800000) / 8388608 AS is_quoted_identifier_on 
,CONVERT(INT,d.status2 & 0x20000) / 131072 AS is_recursive_triggers_on 
,CONVERT(INT,d.status2 & 0x2000000) / 33554432 AS is_cursor_close_on_commit_on 
,CONVERT(INT,d.status2 & 0x100000) / 1048576 AS is_local_cursor_default 
,CONVERT(INT,d.status2 & 0x20000000) / 536870912 AS is_fulltext_enabled 
,DATABASEPROPERTYEX(d.name, 'IsParameterizationForced') AS is_parameterization_forced 
,CONVERT(INT,d.category & 0x1) / 1 AS is_published_snapshot_or_transactional 
,CONVERT(INT,d.category & 0x2) / 2 AS is_subscribed_snapshot_or_transactional 
,CONVERT(INT,d.category & 0x4) / 4 AS is_published_merge 
,CONVERT(INT,d.category & 0x8) / 8 AS is_subscribed_merge 
,CONVERT(INT,d.category & 0x10) / 16 AS is_distribution_database 
,DATABASEPROPERTYEX(d.name, 'IsSyncWithBackup') AS is_sync_with_backup 
,DATABASEPROPERTYEX(d.name, 'LCID') AS default_language_lcid 
,d.version 
,d.filename 
,d.reserved -- reserved, useless 
,d.category 
,d.mode -- locking column, useless 
,d.status 
,d.status2 
FROM sysdatabases d

Something Is Missing

A brief examination of the two queries will show that there is much more information returned from sys.databases that there is from sysdatabases. While some of the information can be gleamed through the use of DATABASEPROPERTYEX, there are about thirty columns more than are either lacking or provide an incomplete picture of the property, such as page verify versus checksum. Many of the columns that are missing relate to features introduced since SQL Server 2005 and forward, such as service broker and contained databases.

Table 1 – Columns missing from sysdatabase

Summary

In this post, we discussed the use of sysdatabases and that it has been replaced by the catalog view sys.databases. While it maybe simple to continue using sysdatabases, there is a lot that you miss out on by not using the catalog view. As demonstrated, the information is much more complex through the compatibility view versus the catalog view. Also, there is a lot of information missing from the catalogue. By making the change, you can simplify your code and get access to more information.

Do you see any reason to continue using sysdatabases? Is there anything missing from this post that people continuing to use the compatibility view should know?

Original article: Lost in Translation – Deprecated System Tables – sysdatabases

©2012 Strate SQL. All Rights Reserved.

Related posts:

1. Lost in Translation – Deprecated System Tables – sysconfigures
2. Lost in Translation – Deprecated System Tables – syscurconfigs
3. Lost in Translation – Deprecated System Tables – sysaltfiles