System Objects

System Database

Master Database

Data Tables

spt_values

The spt_values table is not mentioned in the the SQL Server documentation but it goes back to the Sybase days. master..spt_values contains all the various bits and pieces that the said system procedures need, in an SQL table, to produce the various reports. The sp means system procedure; spt means tables for system procedures; and of course values is the content.

It is 26 or so logical Lookup tables, each beautifully Normalised, folded into one physical table, with aTypecolumn to differentiate the logical tables.

• "L" stands for for LockType Lookup;

• "V" stands for DeviceType Lookup (V is short for Device throughout the server); etc. Type "P2" contains bitwise ordinals, for the expansion of bits that are packed into an INT.

• A set of consecutive numbers within known bounds, that is available in the form of an SQL table is required, in order to perform a Projection,

To get a list of integers from 0 to 2047

SELECT
        number
FROM    
        master..spt_values
WHERE    
        TYPE = 'p';

To get a list of integers from 0 to 9

SELECT
        number
FROM    
        master..spt_values
WHERE    
        TYPE = 'p'
        AND number BETWEEN 0 AND 9;

System Views

System dynamic management views

sys.dm_os_performance_counters

Returns a row per performance counter maintained by the server.

To get a list of deprecated SQL Server Database Engine features,

SELECT 
        * 
FROM 
        sys.dm_os_performance_counters   
WHERE 
        object_name = 'SQLServer:Deprecated Features';