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:
|
1 2 3 4 5 6 7 8 |
USE tempdb; CREATE TABLE dbo.hidden_table ( hidden_table_data varchar(30) NOT NULL CONSTRAINT hidden_table_data_pk PRIMARY KEY CLUSTERED ) ON [PRIMARY]; |
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:
|
1 2 3 4 5 6 |
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.
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DECLARE @cmd nvarchar(max); SET @cmd = N''; SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N' UNION ALL ' 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!
I my case they were just moved to system.tables folder and completely visible