Introduction to System Tables
System tables in SQL Server store the metadata for the database. This metadata describes the database itself, including its objects (tables, views, stored procedures, etc.), users, permissions, and other configuration settings. These tables are essential for the functioning of SQL Server and should be managed with care.
System tables are typically prefixed with sys.. They are located in the master database for server-level information and in each user database for database-specific information.
Categories of System Tables
System tables can be broadly categorized based on the information they contain:
- Object Catalog Views: Provide detailed information about database objects like tables, columns, indexes, constraints, etc. (e.g.,
sys.tables,sys.columns,sys.indexes). - Security Catalog Views: Contain information about users, logins, roles, and permissions (e.g.,
sys.database_principals,sys.server_principals,sys.permissions). - System Configuration: Store settings and configuration options for SQL Server (e.g.,
sys.configurations). - System Processes: Provide information about currently running processes and sessions (e.g.,
sys.dm_exec_sessions,sys.dm_exec_requests).
Commonly Used System Tables
Here are some of the most frequently referenced system tables:
| Table Name | Description |
|---|---|
sys.tables |
Contains a row for each user-defined table and system table in the database. |
sys.columns |
Contains a row for each column in a database. |
sys.objects |
Contains a row for each user-defined schema-scoped object in the database. |
sys.schemas |
Contains a row for each schema in the database. |
sys.indexes |
Contains a row for each index and heap in the database. |
sys.database_principals |
Contains a row for each security principal (user, role, etc.) in the database. |
sys.sql_modules |
Contains one row per Transact-SQL module (stored procedure, function, trigger, view). |
sys.dm_exec_sessions |
Returns information about all active user sessions. |
Example: Querying Table Information
To retrieve the names and object IDs of all user tables in the current database, you can use the following T-SQL query:
SELECT
name AS TableName,
object_id AS TableObjectID
FROM
sys.tables
WHERE
type = 'U'; -- 'U' denotes a user table
Important Considerations
- The structure and availability of system tables can change between SQL Server versions. Always consult the documentation specific to your SQL Server version.
- Directly modifying system tables is not supported and can lead to database corruption. Use provided system stored procedures or catalog views for querying and management.
- Catalog views (e.g.,
sys.tables) are the recommended way to access metadata, as they provide a more stable and backward-compatible interface compared to older system tables (e.g.,sysobjects).