title: SQL Server 2014: Re-Create System view master.dbo.spt_values
body: <p>On my test SQL Server 2014 installation, I was "cleaning" the master database.</p>
  
  <p>With the following command, I was checking which <strong>user objects</strong> there are:</p>
  
  <pre><code>SELECT
  'DROP ' +
      CASE 
          WHEN [sys].[all_objects].type IN ('AF','FN','FS','FT','IF','TF') THEN 'FUNCTION '
      WHEN [sys].[all_objects].type IN ('D','C','F','PK','UQ') THEN 'CONSTRAINT '
      WHEN [sys].[all_objects].type IN ('IT','S','U') THEN 'TABLE '
      WHEN [sys].[all_objects].type IN ('P','PC','RF','X') THEN 'PROCEDURE '
      WHEN [sys].[all_objects].type IN ('TA','TR') THEN 'TRIGGER '
      WHEN [sys].[all_objects].type = 'R' THEN 'RULE '
      WHEN [sys].[all_objects].type = 'SN' THEN 'SYNONYM '
      WHEN [sys].[all_objects].type = 'TT' THEN 'TYPE '
      WHEN [sys].[all_objects].type = 'V' THEN 'VIEW '
  END  +
      SCHEMA_NAME(sys.[all_objects].[schema_id]) + '.' + OBJECT_NAME(object_id) + '; ' as [Command], 
      OBJECT_NAME(object_id) as [ObjectName], 
      [sys].[all_objects].[type_desc] as [TypeDesc], 
      [sys].[all_objects].[type] as [Type], 
      SCHEMA_NAME(sys.[all_objects].[schema_id]) as [Schema]
  FROM 
      sys.[all_objects] WITH (NOLOCK) 
  WHERE SCHEMA_NAME(sys.[all_objects].[schema_id]) like '%dbo%'
  </code></pre>
  
  <p>One of the results was the view spt_values. <br><br></p>
  
  <pre><code>Command                 | ObjectName | TypeDesc | Type | Schema
  ------------------------|------------|----_-----|------|-------
  DROP VIEW dbo.spt_values; spt_values   VIEW       V      dbo
  </code></pre>
  
  <p>As it was not one of the views I knew, I deleted it (along with other objects).</p>
  
  <p>Later that day, I wanted to check the properties of a database in SSMS 2016 and got the following error:</p>
  
  <p><a href="https://i.stack.imgur.com/VAoXR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VAoXR.png" alt="Invalid object name &#39;master.dbo.spt_values&#39;"></a></p>
  
  <p>After some searching, I found that I could recreate the missing view with the script u_tables.sql (which is in the SQL Server installation folder on your server). Information from here: <a href="https://ashishgilhotra.wordpress.com/tag/u_tables-sql/" rel="nofollow noreferrer">https://ashishgilhotra.wordpress.com/tag/u_tables-sql/</a></p>
  
  <p>The code in that script to create the view is the following:</p>
  
  <pre><code>create view spt_values as
  select name collate database_default as name,
      number,
      type collate database_default as type,
      low, high, status
  from sys.spt_values
  go
  
  EXEC sp_MS_marksystemobject 'spt_values'
  go
  
  grant select on spt_values to public
  go
  </code></pre>
  
  <p>Already when looking at the code, I doubted that it would work, as there is no sys.spt_values table anywhere to be found.</p>
  
  <p>As expected I get the error</p>
  
  <pre><code>Msg 208, Level 16, State 1, Procedure spt_values, Line 6
  Invalid object name 'sys.spt_values'.
  </code></pre>
  
  <p>On my other server with SQL Server 2008 on it, there is a table master.dbo.spt_values (but no view)!</p>
  
  <p>After some more searching, I found that I could just create a table with the same name.. Link here <a href="https://www.mssqltips.com/sqlservertip/3694/fix-invalid-object-name-masterdbosptvalues-when-viewing-sql-server-database-properties/" rel="nofollow noreferrer">https://www.mssqltips.com/sqlservertip/3694/fix-invalid-object-name-masterdbosptvalues-when-viewing-sql-server-database-properties/</a></p>
  
  <p>Now I create a table with the values from another SQL Server 2014 installation, and everything seems to be working again.</p>
  
  <p>But, it is <strong>not</strong> <strong>correct</strong>!</p>
  
  <p>When I check the new created object on the test server with this command</p>
  
  <pre><code>select [name] , [type], [type_desc]
  from sys.objects
  where name like 'spt_v%'
  </code></pre>
  
  <p>It shows a user_table object. On my other server, it shows a view...</p>
  
  <p>So, my question is: How can I create the view spt_values which gets its data from a table spt_values?</p>