SQL Server System Tables or System Catalog Views

System Tables

SQL Server uses a collection of system tables to track information about users, database, tables, replication task etc.

SQL Server System Tables, also referred to as system catalog views, provide metadata about the database itself. These tables are part of the system database, specifically the "master" database, and they store information about various database objects, security settings, configurations, and more. System tables play a crucial role in managing and querying information about the SQL Server instance. There are different types of system tables:

System Base Tables:

These are the actual physical tables that store system information. They are typically named with a "sys" prefix. For example, sysobjects, syscolumns, sysindexes, etc.

However, starting with SQL Server 2005, Microsoft introduced system catalog views as a more flexible and recommended way to access metadata. The system base tables are still present for backward compatibility.

System Catalog Views: 

System catalog views are dynamic management views that expose metadata in a more standardized and user-friendly way compared to the older system tables.

Examples of system catalog views include sys.tables, sys.columns, sys.indexes, and others. These views are part of the INFORMATION_SCHEMA schema.

Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs):

Introduced in SQL Server 2005, DMVs and DMFs provide real-time information about the SQL Server instance, server health, and performance.

Examples include sys.dm_exec_sessions, sys.dm_os_wait_stats, and others. These views and functions are prefixed with dm_ or dmf_.

Some commonly used system catalog views include: 

• tables: Contains information about all user-defined and system tables.
• columns: Stores information about columns in user-defined and system tables.
• indexes: Provides details about indexes on tables.
• views: Contains information about views.
• procedures: Stores information about stored procedures.
• triggers: Contains information about triggers.
• schemas: Provides information about database schemas.
• databases: Contains information about databases on the SQL Server instance. 

Querying System Tables

DML operations on system tables are discouraged, as it may lead to unexpected behavior and potential issues with system stability. Instead, they should be primarily used to query system Meta information. For example we can query to retrieve information about tables in a database using system catalog views:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

This query retrieves information about all user-defined tables in the current database using the INFORMATION_SCHEMA.TABLES system catalog view.

Points to Remember

1. The master system database contains a set of tables with information about databases, login server and other system wide information.
2. Each database contains a set of system tables with information about all the objects contained within it.
3. The system tables found in each database begin with sys word followed by XXX where XXX stands for servers allocations columns comments constants depends file groups files foreign keys full text catalogue indexes index keys members object permission protects references types users. Note that they all XXX ends with s word to pluralize them.
4. You can query a system table to fetch some vital information about the SQL Server system.

In the next post we will learn about Constraints in SQL Server.