Hidden tables in SSMS: detect and create them!

Did you know hidden tables may be lurking in your database? SQL Server Management Studio is a world-class database management toolset, and includes some really great features. Arguably the most-used feature is the Object Explorer – that handy left-most pane in the user interface that lists the servers/databases/tables, etc. Most users, except perhaps those with a very large number of servers or databases to manage, use this tool as a trusted interface into the objects that exist in any given database. However, there exists a way to “hide” objects from exposure in the Object Explorer simply by giving those objects an extended property named ‘microsoft_database_tools_support’.

Take the following quick example:

If you look at tempdb in the Object Explorer, you’ll see the hidden_table listed.

hidden_table is visible in the list of tables.

Now, if we add the extended property to the hidden_table object, via sys.sp_addextendedproperty, as in this code:

and refresh the list of tables in SSMS, we no longer see hidden_table:

hidden_table is no longer visible.

For completeness, this code shows how to drop the extended property. I don’t recommend using this against tables that have been hidden from view in SSMS by Microsoft, since that might result in an unsupported configuration.

The code below can be used to see all the objects, server-wide, that have the microsoft_database_tools_support extended property set, and thus are “invisible” inside SQL Server Management Studio:

Microsoft marks the database diagramming support objects with the microsoft_database_tools_support extended property, which means you’ll never see the following objects listed in SSMS:

  • Tables:
    • dbo.sysdiagrams
  • Stored Procedures:
    • dbo.sp_upgraddiagrams
    • dbo.sp_helpdiagrams
    • dbo.sp_helpdiagramdefinition
    • dbo.sp_creatediagram
    • dbo.sp_renamediagram
    • dbo.sp_alterdiagram
    • dbo.sp_dropdiagram
  • Functions:
    • dbo.fn_diagramobjects

SQL Server Data Tools (SSDT) uses the microsoft_database_tools_support extended property to hide the dbo.__RefactorLog table from view. This table is automatically created in the database whenever a refactor operation is conducted in SSDT.

Let me know in the comments below if you find any other tool or product hiding objects with the microsoft_database_tools_support extended property, and I’ll add those details to this post!

Ads by Google, Paying the Rent: