SQL Server v.Next (Denali) : How has sys.databases changed?

Earlier, I documented the system objects that have changed in Denali. One that caught my eye was a slew of new columns made available through sys.databases, mostly to support a feature I also blogged about recently, contained databases. Consider the following simple query:

 SELECT * FROM sys.databases;

Yes, this comes from a DMV, but where does the DMV really get its data? First I wanted to view the execution plans in 2008 R2 and Denali to compare them: were there any significant differences? Opening them in Management Studio proved to be relatively useless… to see everything on the screen, I can tell that they're a bit different, but zooming in and scrolling all over the place is for the birds:

 
<img src="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.41.3320PM.png" width="619" border="1" height="230"> 
<i>2008 R2 graphical plan in SSMS</i>
 
 
<a href="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.40.5120PM.png" title="http://sqlblog.com/files/folders/31085/download.aspx" target="_blank" rel="noopener noreferrer"><img src="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.40.5120PM-1.png" width="619" border="1" height="220"></a>
<i>Denali graphical plan in SSMS</i>

So I opened my trusty SQL Sentry Plan Explorer (which I've blogged about before) and got a much better quick view of these plans, without having to perform any zooming whatsoever:

 
<a href="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.38.3220PM.png" title="http://sqlblog.com/files/folders/31086/download.aspx" target="_blank" rel="noopener noreferrer"><img src="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.38.3220PM-1.png" width="619" border="1" height="79"></a> 
<i>2008 R2 graphical plan in Plan Explorer</i>
 
 
<a href="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.38.4820PM.png" title="http://sqlblog.com/files/folders/31085/download.aspx" target="_blank" rel="noopener noreferrer"><img src="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.38.4820PM-1.png" width="619" border="1" height="80"></a> 
<i>Denali graphical plan in Plan Explorer</i>

So this looks interesting so far; the execution plan for Denali is much busier, and has some references to internal objects I haven't noticed before: table-valued functions SYSLANG and SYSFULLTEXTLANGUAGES, and a system table called sysguidrefs. You can see the actual definition of the view pretty simply, by issuing:

 SELECT OBJECT_DEFINITION(OBJECT_ID('sys.databases'));

In SQL Server 2008 R2, you get this (about 4,200 characters):

 CREATE VIEW sys.databases AS
   SELECT d.name, d.id AS database_id,
       r.indepid AS source_database_id,
       d.sid AS owner_sid,
       d.crdate AS create_date,
       d.cmptlevel AS compatibility_level,
       CONVERT(SYSNAME, CollationPropertyFromID(p.cid, 'name')) AS collation_name,
       p.user_access, ua.name AS user_access_desc,
       sysconv(bit, d.status &amp; 0x400) AS is_read_only,          -- DBR_RDONLY
       sysconv(bit, d.status &amp; 1) AS is_auto_close_on,          -- DBR_CLOSE_ON_EXIT
       sysconv(bit, d.status &amp; 0x400000) AS is_auto_shrink_on,      -- DBR_AUTOSHRINK
       p.state, st.name AS state_desc,
       sysconv(bit, d.status &amp; 0x200000) AS is_in_standby,      -- DBR_STANDBY
       sysconv(bit, d.status &amp; 0x40000000) AS is_cleanly_shutdown,  -- DBR_CLEANLY_SHUTDOWN
       sysconv(bit, d.status &amp; 0x80000000) AS is_supplemental_logging_enabled,  -- DBR_SUPPLEMENT_LOG
       p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc,
       sysconv(bit, d.status &amp; 0x800000) AS is_read_committed_snapshot_on,      -- DBR_READCOMMITTED_SNAPSHOT
       p.recovery_model, ro.name AS recovery_model_desc,
       p.page_verify_option, pv.name AS page_verify_option_desc,
       sysconv(bit, d.status2 &amp; 0x1000000) AS is_auto_create_stats_on,          -- DBR_AUTOCRTSTATS
       sysconv(bit, d.status2 &amp; 0x40000000) AS is_auto_update_stats_on,     -- DBR_AUTOUPDSTATS
       sysconv(bit, d.status2 &amp; 0x80000000) AS is_auto_update_stats_async_on,   -- DBR_AUTOUPDSTATSASYNC
       sysconv(bit, d.status2 &amp; 0x4000) AS is_ansi_null_default_on,         -- DBR_ANSINULLDFLT
       sysconv(bit, d.status2 &amp; 0x4000000) AS is_ansi_nulls_on,             -- DBR_ANSINULLS
       sysconv(bit, d.status2 &amp; 0x2000) AS is_ansi_padding_on,                  -- DBR_ANSIPADDING
       sysconv(bit, d.status2 &amp; 0x10000000) AS is_ansi_warnings_on,         -- DBR_ANSIWARNINGS
       sysconv(bit, d.status2 &amp; 0x1000) AS is_arithabort_on,                    -- DBR_ARITHABORT
       sysconv(bit, d.status2 &amp; 0x10000) AS is_concat_null_yields_null_on,      -- DBR_CATNULL
       sysconv(bit, d.status2 &amp; 0x800) AS is_numeric_roundabort_on,         -- DBR_NUMEABORT
       sysconv(bit, d.status2 &amp; 0x800000) AS is_quoted_identifier_on,           -- DBR_QUOTEDIDENT
       sysconv(bit, d.status2 &amp; 0x20000) AS is_recursive_triggers_on,           -- DBR_RECURTRIG
       sysconv(bit, d.status2 &amp; 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM
       sysconv(bit, d.status2 &amp; 0x100000) AS is_local_cursor_default,           -- DBR_DEFLOCALCURS
       sysconv(bit, d.status2 &amp; 0x20000000) AS is_fulltext_enabled,         -- DBR_FTENABLED
       sysconv(bit, d.status2 &amp; 0x200) AS is_trustworthy_on,                -- DBR_TRUSTWORTHY
       sysconv(bit, d.status2 &amp; 0x400) AS is_db_chaining_on,                -- DBR_DBCHAINING
       sysconv(bit, d.status2 &amp; 0x08000000) AS is_parameterization_forced,  -- DBR_UNIVERSALAUTOPARAM
       sysconv(bit, d.status2 &amp; 64) AS is_master_key_encrypted_by_server,   -- DBR_MASTKEY
       sysconv(bit, d.category &amp; 1) AS is_published,
       sysconv(bit, d.category &amp; 2) AS is_subscribed,
       sysconv(bit, d.category &amp; 4) AS is_merge_published,
       sysconv(bit, d.category &amp; 16) AS is_distributor,
       sysconv(bit, d.category &amp; 32) AS is_sync_with_backup,
       d.svcbrkrguid AS service_broker_guid,
       sysconv(bit, CASE WHEN d.scope = 0 THEN 1 ELSE 0 END) AS is_broker_enabled,
       p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
       sysconv(bit, d.status2 &amp; 4) AS is_date_correlation_on,       -- DBR_DATECORRELATIONOPT
       sysconv(bit, d.category &amp; 64) AS is_cdc_enabled,
       sysconv(bit, d.status2 &amp; 0x100) AS is_encrypted,                     -- DBR_ENCRYPTION
       sysconv(bit, d.status2 &amp; 0x8) AS is_honor_broker_priority_on             -- DBR_HONORBRKPRI
   FROM master.sys.sysdbreg d OUTER APPLY OPENROWSET(TABLE DBPROP, d.id) p
   LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB
   LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
   LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
   LEFT JOIN sys.syspalvalues si  ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
   LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
   LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
   LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
   WHERE d.id &lt; 0x7fff
       AND has_access('DB', d.id) = 1

And in Denali, it's much more verbose – almost 50% larger (about 6,200 characters):

 CREATE VIEW sys.databases AS
   SELECT d.name, d.id AS database_id,
       r.indepid AS source_database_id,
       d.sid AS owner_sid,
       d.crdate AS create_date,
       d.cmptlevel AS compatibility_level,
       CONVERT(SYSNAME, CollationPropertyFromID(p.cid, 'name')) AS collation_name,
       p.user_access, ua.name AS user_access_desc,
       sysconv(bit, d.status &amp; 0x400) AS is_read_only,          -- DBR_RDONLY
       sysconv(bit, d.status &amp; 1) AS is_auto_close_on,          -- DBR_CLOSE_ON_EXIT
       sysconv(bit, d.status &amp; 0x400000) AS is_auto_shrink_on,      -- DBR_AUTOSHRINK
       p.state, st.name AS state_desc,
       sysconv(bit, d.status &amp; 0x200000) AS is_in_standby,      -- DBR_STANDBY
       p.is_cleanly_shutdown,
       sysconv(bit, d.status &amp; 0x80000000) AS is_supplemental_logging_enabled,  -- DBR_SUPPLEMENT_LOG
       p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc,
       sysconv(bit, d.status &amp; 0x800000) AS is_read_committed_snapshot_on,      -- DBR_READCOMMITTED_SNAPSHOT
       p.recovery_model, ro.name AS recovery_model_desc,
       p.page_verify_option, pv.name AS page_verify_option_desc,
       sysconv(bit, d.status2 &amp; 0x1000000) AS is_auto_create_stats_on,          -- DBR_AUTOCRTSTATS
       sysconv(bit, d.status2 &amp; 0x40000000) AS is_auto_update_stats_on,     -- DBR_AUTOUPDSTATS
       sysconv(bit, d.status2 &amp; 0x80000000) AS is_auto_update_stats_async_on,   -- DBR_AUTOUPDSTATSASYNC
       sysconv(bit, d.status2 &amp; 0x4000) AS is_ansi_null_default_on,         -- DBR_ANSINULLDFLT
       sysconv(bit, d.status2 &amp; 0x4000000) AS is_ansi_nulls_on,             -- DBR_ANSINULLS
       sysconv(bit, d.status2 &amp; 0x2000) AS is_ansi_padding_on,                  -- DBR_ANSIPADDING
       sysconv(bit, d.status2 &amp; 0x10000000) AS is_ansi_warnings_on,         -- DBR_ANSIWARNINGS
       sysconv(bit, d.status2 &amp; 0x1000) AS is_arithabort_on,                    -- DBR_ARITHABORT
       sysconv(bit, d.status2 &amp; 0x10000) AS is_concat_null_yields_null_on,      -- DBR_CATNULL
       sysconv(bit, d.status2 &amp; 0x800) AS is_numeric_roundabort_on,         -- DBR_NUMEABORT
       sysconv(bit, d.status2 &amp; 0x800000) AS is_quoted_identifier_on,           -- DBR_QUOTEDIDENT
       sysconv(bit, d.status2 &amp; 0x20000) AS is_recursive_triggers_on,           -- DBR_RECURTRIG
       sysconv(bit, d.status2 &amp; 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM
       sysconv(bit, d.status2 &amp; 0x100000) AS is_local_cursor_default,           -- DBR_DEFLOCALCURS
       sysconv(bit, d.status2 &amp; 0x20000000) AS is_fulltext_enabled,         -- DBR_FTENABLED
       sysconv(bit, d.status2 &amp; 0x200) AS is_trustworthy_on,                -- DBR_TRUSTWORTHY
       sysconv(bit, d.status2 &amp; 0x400) AS is_db_chaining_on,                -- DBR_DBCHAINING
       sysconv(bit, d.status2 &amp; 0x08000000) AS is_parameterization_forced,  -- DBR_UNIVERSALAUTOPARAM
       sysconv(bit, d.status2 &amp; 64) AS is_master_key_encrypted_by_server,   -- DBR_MASTKEY
       sysconv(bit, d.category &amp; 1) AS is_published,
       sysconv(bit, d.category &amp; 2) AS is_subscribed,
       sysconv(bit, d.category &amp; 4) AS is_merge_published,
       sysconv(bit, d.category &amp; 16) AS is_distributor,
       sysconv(bit, d.category &amp; 32) AS is_sync_with_backup,
       d.svcbrkrguid AS service_broker_guid,
       sysconv(bit, CASE WHEN d.scope = 0 THEN 1 ELSE 0 END) AS is_broker_enabled,
       p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
       sysconv(bit, d.status2 &amp; 4) AS is_date_correlation_on,       -- DBR_DATECORRELATIONOPT
       sysconv(bit, d.category &amp; 64) AS is_cdc_enabled,
       sysconv(bit, d.status2 &amp; 0x100) AS is_encrypted,                     -- DBR_ENCRYPTION
       CONVERT(bit, d.status2 &amp; 0x8) AS is_honor_broker_priority_on,                -- DBR_HONORBRKPRI
       sgr.guid AS replica_id,
       sgr2.guid AS group_database_id,
       default_language_lcid = CASE WHEN ((d.status2 &amp; 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(smallint, p.default_language) ELSE NULL END,
       default_language_name = CASE WHEN ((d.status2 &amp; 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(SYSNAME, sld.name) ELSE NULL END,
       default_fulltext_language_lcid = CASE WHEN ((d.status2 &amp; 0x80000)=0x800000 AND p.containment = 1) THEN CONVERT(INT, p.default_fulltext_language) ELSE NULL END,
       default_fulltext_language_name = CASE WHEN ((d.status2 &amp; 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(SYSNAME, slft.name) ELSE NULL END,
       is_nested_triggers_on = CASE WHEN ((d.status2 &amp; 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(bit, p.allow_nested_triggers) ELSE NULL END,
       is_transform_noise_words_on = CASE WHEN ((d.status2 &amp; 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(bit, p.transform_noise_words) ELSE NULL END,
       two_digit_year_cutoff = CASE WHEN ((d.status2 &amp; 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(smallint, p.two_digit_year_cutoff) ELSE NULL END,
       containment = sysconv(tinyint, (d.status2 &amp; 0x80000)/0x80000), -- DBR_IS_CDB
       containment_desc = CONVERT(NVARCHAR(60), cdb.name)
   FROM master.sys.sysdbreg d OUTER APPLY OPENROWSET(TABLE DBPROP, d.id) p
   LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB
   LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
   LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
   LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
   LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
   LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
   LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
   LEFT JOIN sys.syssingleobjrefs agdb ON agdb.depid = d.id AND agdb.class = 104 AND agdb.depsubid = 0    -- SRC_AVAILABILITYGROUP 
   LEFT JOIN master.sys.sysclsobjs  ag ON ag.id = agdb.indepid AND ag.class = 67 -- SOC_AVAILABILITY_GROUP
   LEFT JOIN master.sys.sysguidrefs sgr ON sgr.class = 8 AND sgr.id = ag.id AND sgr.subid = 1 -- GRC_AGGUID / AGGUID_REPLICA_ID
   LEFT JOIN master.sys.sysguidrefs sgr2 ON sgr2.class = 9 AND sgr2.id = ag.id AND sgr2.subid = d.id -- GRC_AGDBGUID
   LEFT JOIN sys.syspalvalues cdb ON cdb.class = 'DCDB' AND cdb.value = CASE WHEN (d.status2 &amp; 0x80000)=0x80000 THEN 1 ELSE 0 END
   LEFT JOIN sys.syslanguages sld ON sld.lcid = p.default_language
   LEFT JOIN sys.fulltext_languages slft ON slft.lcid = p.default_fulltext_language
   WHERE d.id &lt; 0x7fff
       AND has_access('DB', d.id) = 1

But of course you can't dig much deeper than this. If you try and see into objects like sys.singleobjectrefs and sys.sysdbreg, you get the SQL Server equivalent to a 404 Not Found error. This is even true if you use an old hack to view the system resource db directly (which I've described before).

So there isn't all that much value in this after all, unless you're an avid explorer. For me, I like to dig in as far as I can to see how things work, but I get pretty aggravated when I see syntax that works in system objects, but that we can't use ourselves (such as "hidden" OPENROWSET() calls) or things that seem custom for no obvious reason, such as the sysconv() function – why don't they just use CONVERT()?

More importantly, I have a guilty pleasure in seeing bad practices in practice (or at least inconsistency). Notice in the above code that the new columns for database containment properties have been added using "alias = expression" syntax, whereas the existing code had exclusively used "expression as alias" notation. They also should be using statement terminators, if for no other reason than to demonstrate that they really mean it when they say they will be required in some future version of SQL Server (see this rant from last year).

There are other things that irk me too – lack of AS for table/view aliases, use of LEFT JOIN instead of LEFT OUTER JOIN, inconsistent spacing, sometimes putting more than one output column on the same line, sloppy indenting and alignment… it surprises me when my own code quality and consistency standards would reject just about every module that ships in SQL Server. It might take 5 extra minutes to polish this view up, and if I were responsible for it, I would simply squeeze it into my deadline. Of course I don't have much insight into the Microsoft development cycle, but I do know that a lot of – let's say – "less than stellar" code comes out of those offices. The stuff above is pretty tame in comparison.

Probably the most important thing *YOU* should be aware of: there are extra columns here, so if you are currently building #temp tables or auditing scripts with the previous schema of sys.databases in mind, you'll need to be prepared for this change so that your existing scripts don't break.

AaronBertrand says:

December 1, 2010 at 11:25 PM

No, that's me, I keep forgetting about the "other" DAC. DUH! <…off to play…>
Kalen Delaney says:

December 1, 2010 at 11:11 PM

Hi Aaron
Thanks for the great couple of posts about Denali metadata… I am waiting until I get back home after my 5 months in Boston to install Denali.
I'm wondering if I'm missing something when you mention using the resource db to see sys.sysdbreg, etc. Is this another change in Denali? In previous versions I can just use the DAC to see the contents of the system tables.
~Kalen