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:

USE tempdb;

CREATE TABLE dbo.hidden_table
    hidden_table_data varchar(30) NOT NULL
        CONSTRAINT hidden_table_data_pk

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:

EXEC sys.sp_addextendedproperty @name = N'microsoft_database_tools_support'
    , @value = NULL
    , @level0type = 'SCHEMA'
    , @level0name = 'dbo'
    , @level1type = 'TABLE'
    , @level1name = 'hidden_table';

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.

EXEC sys.sp_dropextendedproperty @name = N'microsoft_database_tools_support'
    , @level0type = 'SCHEMA'
    , @level0name = 'dbo'
    , @level1type = 'TABLE'
    , @level1name = 'hidden_table';

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:

DECLARE @cmd nvarchar(max);
SET @cmd = N'';
SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
' END + '
SELECT ServerName = N''' + @@SERVERNAME + ''' COLLATE SQL_Latin1_General_CP1_CI_AS
    , DatabaseName = N''' + d.name + ''' COLLATE SQL_Latin1_General_CP1_CI_AS
    , ObjectName = s.name + N''.'' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS
    , ObjectType = o.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS
    , ep.value
FROM ' + QUOTENAME(d.name) + '.sys.extended_properties ep
    LEFT JOIN ' + QUOTENAME(d.name) + '.sys.objects o ON ep.major_id = o.object_id
    LEFT JOIN ' + QUOTENAME(d.name) + '.sys.schemas s ON o.schema_id = s.schema_id
WHERE ep.name = N''microsoft_database_tools_support''
FROM sys.databases d
WHERE d.state_desc = 'ONLINE'
ORDER BY d.name;
EXEC sys.sp_executesql @cmd;

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!