What are the MSSQL critical system tables

  • SQL Server like all other SQL Engines use metadata to know where the data is stored, how it's stored and how to understand it. See: Wikipedia definition of metadata.
  • The metadata is the most critical part of the system tables, without it, SQL Server does not even know where the data files are.

SQL Server 2000

The critical system tables are:

  1. sysindexes: The sysobjects table has a row for each object, such as user tables, system tables, constraints, views, stored procedures ...
  2. sysobjects: The sysindexes table has a row for each index and table in the database.
  3. syscolumns: The syscolumns table has a row for each table column or view column and a row for each stored procedure parameter.

SQL Server 2005 and SQL Server 2008

The critical system tables are:

  1. sysallocunits: The sysallocunits has a row for each storage allocation unit.
  2. syshobts: HOBT stands for heap or B-tree. The syshobts table has a row for each HOBT that can host multiple rowsets.
  3. syshobtcolumns: The syshobtcolumns table has a row for each column of a HOBT.
  4. sysrowsets: The sysrowsets table has a row for each partition rowset for an index or a table without a clustered index.
  5. sysrowsetcolumns: The sysrowsetcolumns table has a row for each column, of partition rowset.

These tables are hidden and cannot be directly queried:

  1. SELECT * FROM sysallocunits;
  2. go
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysallocunits'.

But they are used by dbcc.