Microsoft SQL Server 2016 - SSMS, cannot expand tables for a database

Question

There are many databases on my server, recently of them one (which I am the dbo on) has become inaccessible through SSMS. When I expand the tables node in 'object explorer' for this database I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (.Net SqlClient Data Provider)

However, I can execute selects successfully on that database and other users are not having this problem.

I have;

1. Checked my login for incorrect settings.
2. Checked my user for incorrect settings.
3. Restarted the SQL Server Engine.
4. Repaired SSMS using the installer utility.

Would very much appreciate your input, I am stumped (this has never happened before).

EDIT: After tracing using SQL Profiler and causing this event I extracted the query and ran it in a SSMS session window. The returned results are from the wrong database.

EDIT: Further to this, I ran dbcc checkdb(databasename) and no errors were returned, I then did the same for master, no errors were returned.

EDIT: The query being executed when I expand the table folder:

    exec sp_executesql N'SELECT
tbl.name AS [Name],
SCHEMA_NAME(tbl.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
        serverproperty(N''Servername'')
       AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/Table[@Name='' + quotename(tbl.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(tbl.schema_id),'''''''') + '']'' AS [Urn],
tbl.create_date AS [CreateDate],
CAST(tbl.is_memory_optimized AS bit) AS [IsMemoryOptimized],
CAST(CASE idx.type WHEN 5 THEN 1 ELSE 0 END AS bit) AS [HasClusteredColumnStoreIndex],
CAST(tbl.is_remote_data_archive_enabled AS bit) AS [RemoteDataArchiveEnabled],
tbl.temporal_type AS [TemporalType],
CAST(CASE WHEN ''PS''=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],
CAST(
        ISNULL((SELECT 1 from sys.all_columns
                WHERE object_id = tbl.object_id 
                AND is_sparse = 1), 0)
       AS bit) AS [HasSparseColumn]
FROM
sys.tables AS tbl
LEFT OUTER JOIN sys.periods as periods ON periods.object_id = tbl.object_id
LEFT OUTER JOIN sys.tables as historyTable ON historyTable.object_id = tbl.history_table_id
INNER JOIN sys.indexes AS idx ON 
        idx.object_id = tbl.object_id and (idx.index_id < @_msparam_0  or (tbl.is_memory_optimized = 1 and idx.index_id = (select min(index_id) from sys.indexes where object_id = tbl.object_id)))

LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id
WHERE
(CAST(
 case 
    when tbl.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = tbl.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N''microsoft_database_tools_support'') 
        is not null then 1
    else 0
end          
             AS bit)=@_msparam_1 and tbl.is_filetable=@_msparam_2 and CAST(tbl.is_memory_optimized AS bit)=@_msparam_3 and tbl.temporal_type=@_msparam_4 and CAST(tbl.is_external AS bit)=@_msparam_5)
ORDER BY
[Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'2',@_msparam_1=N'0',@_msparam_2=N'0',@_msparam_3=N'0',@_msparam_4=N'0',@_msparam_5=N'0'

EDIT: As a work-around, I reinstalled the SSMS 2014. I can access my tables through here.

Answer 1

There is nothing wrong with your database, it's a known issue with SSMS 16.5.2 and has been reported to product team. Please revert back to previous release of SSMS if it causes too much trouble. For more information, please refer to this thread.

Apparently this only happens when the database have table(s) with more than one sparse column.

Answer 2

Try it's

SELECT 
    NAME
FROM [base].SYS.triggerS
WHERE parent_class_desc = 'DATABASE'

Take the trigger name and perform the procedure below

DISABLE TRIGGER [dbo].[trigger_name]