sys.dm_exec_sql_text returns NULL in dbid, objectid, and number columns, but not text

This is something that's bugged me though has never caused any real issues as I can generally locate this information by other means, but can someone explain why NULL may be returned in the dbid, objectid, and number columns from the sys.dm_exec_sql_text DMV, yet yield results in the text column? I understand why output would return inversely (e.g. all columns but text would yield data), but I've seen this behavior a number of times across a number of SQL Server versions and the documentation doesn't seem to imply this should be possible, or I'm just reading it wrong.

Here's an example of the query I'm running:

SELECT TOP 100 
      t.*
    , s.*
    , c.*
FROM    sys.dm_exec_query_stats s
        LEFT JOIN sys.dm_exec_connections c
               ON c.most_recent_sql_handle = s.sql_handle
        CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE s.execution_count > 1 AND DATEDIFF (second, creation_time, GETDATE()) > 0
        AND t.dbid IS NULL

Here's a sample of one of the results from the text column which implies this isn't related to temporary objects which is what I would normally guess to be the cause.

select * from [dbo].[Map_ProviderSpecialty]

What situation is occurring where these columns are returning NULL values?

This is by design as of now. There is a feedback item about this problem.

sys.dm_exec_query_stats DBID column NULL for dynamic SQL - by Theo Ekelmans

But at the end there is a comment:

Thank you for the feedback! We will consider it for the next release
of SQL Server.

There is also some workaround as mentioned by Erik Darling:

If you need that data, try adding CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p -- you should get at least dbid from there, though objectid will still be NULL for adhoc queries.

He also said:

Well, it's sometimes available in two other related views:

• dm_exec_query_plan

• dm_exec_plan_attributes

Those take plan handle instead of SQL handle though

StackExchange Database Administrators Q#171456, answer score: 6