Sys.Indexes are missing from the sys.objects view of SQL Server 2014

I just installed SQL Server 2014.

  • Product: Microsoft SQL Server Express (64-bit)
  • Operating system: Microsoft Windows NT 6.1 (7601)
  • Platform: NT x64
  • Version: 12.0.2000.8
  • Server Sort: Latin1_General_CI_AS
  • Is Clustered: False
  • Is HADR Enabled: False

I created a database by right clicking on Databases -> New Database. I set the name, I left the default options and I clicked OK.

By doing the following query, I realized that there are items from the view sys.indexes

that cannot be found in sys.objects

.

Select * 
From sys.indexes ind
Where not exists ( select * from sys.objects obj where ind.object_id = obj.object_id )

      

The above query returns the following results:

5575058         plan_persist_plan_cidx
5575058         plan_persist_plan_idx1
21575115        plan_persist_runtime_stats_cidx
21575115        plan_persist_runtime_stats_idx1
37575172        plan_persist_runtime_stats_interval_cidx
37575172        plan_persist_runtime_stats_interval_idx1
53575229        plan_persist_context_settings_cidx
2121058592      plan_persist_query_text_cidx
2121058592      plan_persist_query_text_idx1
2137058649      plan_persist_query_cidx
2137058649      plan_persist_query_idx1

      

I have read that non-scoped items are not returned from the view sys.objects

, but I cannot find any information related to the above items. Until today, I have been using SQL Server 2012 and have had no problem with the connection logic above.

What are these items?

How can I distinguish these items from others (other = items returned from sys.objects

)?

Thanks,
Kostas

+3


source to share


1 answer


You can exclude these elements by specifying the IsMsShipped property of the object:



Select * 
From sys.indexes ind
Where not exists ( select * from sys.objects obj where ind.object_id = obj.object_id )
And objectproperty( ind.object_id, 'IsMSShipped' ) = 0

      

0


source







All Articles