Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Add "type" and "type_desc" fields to sys.databases DMV (to make life easier on EVERYONE)

Most anyone who actively works with SQL Server needs, at various times, to do operations across all (or most) of their databases. Typically the system DBs (master, model, msdb, and tempdb, and maybe even mssqlsystemresource) are excluded, but not always. Sometimes there are Microsoft delivered DBs that support a feature of SQL Server, such as the [distribution] DB for Replication, [ReportServer] and [ReportServerTempDB] for SSRS, and now [SSISDB] for SSIS. And sometimes there are demo DBs that one can optionally install, such as: AdventureWorks, AdventureWorks2012, WideWorldImporters, and TicketReservations.

Sometimes you need to do operations on all but the system DBs. Sometimes you want to do operations on just your user DBs and not touch anything from Microsoft, or maybe just exclude the system and feature-related one (but the demo ones might be included).

For any of these operations, we all query sys.databases, filter out the 4 (or 5?) system DBs, sometimes check HAS_DBACCESS, and maybe 1 or 2 other filters. We shouldn't all have to write this same query, over and over again, with minor inconsistencies throughout (some people don't know to exclude [distribution] or [SSISDB] if they have never used those features since those DBs don't get created unless you do use those features).

And no, using [sp_MSforeachdb] is not an option for a variety of reasons.

1 vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    under review  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 1

    <=-=Feb 21 2017 1:58PM=-=>

    Thank you for your suggestion. While I can see that this would be helpful, it realistically won’t happen quickly, as it means changing on-disk layout in Master to hold this information. We will consider this when we have need to make such a change, and will re-evaluate.
    In the meantime, you can filter out system databases by looking for databases with owner_sid <> 0×00.

    <=-=Feb 22 2017 7:23AM=-=>

    @Kevin, thanks for that reply. Regarding the suggestion of using “WHERE [owner_sid] <> 0×00”: I am not sure how this is supposed to help given that the owner_sid on all of my various instances is 0×01 for the system DBs as well as many others. This is one of the reasons that I made this request: I cannot find any values in sys.databases that reliably indicate a “system” DB. Am I missing something?

    <=-=Mar 17 2017 2:50PM=-=>

    If you are wanting to identify master, model, msdb, tempdb, you can filter on database_id > 4
    (the system databases are in the range 1-4)

    If you want to identify other databases which may be created by SQL, like SSISDB then I don’t know of a clean method at this time.
    I will put this on the list for consideration.

    <=-=Mar 21 2017 7:45AM=-=>

    Hi Kevin. Yes, I was wanting to “cleanly” / reliably identify databases that are Microsoft-provided, whether directly (e.g. distribution) or indirectly (e.g. AdventureWorks). Currently this concept exists, albeit in a simpler form than what is needed here, as the following columns from various system catalog views:

    1) sys.objects.is_ms_shipped
    2) sys.assemblies.is_user_defined
    3) sys.servers.is_system
    4) sys.conversation_endpoints.is_system
    5) sys.conversation_groups.is_system
    6) sys.types.is_user_defined

    Thanks for considering this :-).

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base