In modern SQL Server, “system tables” usually means metadata you should query through documented catalog views such as sys.objects, sys.tables, and sys.columns. The phrase can also refer to internal system base tables, but those are Database Engine implementation details—not a supported interface for ordinary customer queries or updates.
What are SQL Server system tables?
The phrase has two meanings that are important to distinguish:
| # | Preview | Product | Price | |
|---|---|---|---|---|
| 1 |
|
SQL Pocket Guide: A Guide to SQL Usage | $21.34 | Buy on Amazon |
| 2 |
|
T-SQL Fundamentals (Developer Reference) | $40.33 | Buy on Amazon |
| 3 |
|
T-SQL Querying (Developer Reference) | $10.76 | Buy on Amazon |
| 4 |
|
Murach's SQL Server 2012 for Developers (Training & Reference) | $26.48 | Buy on Amazon |
| 5 |
|
T-SQL Fundamentals (Developer Reference) | $28.86 | Buy on Amazon |
As an Amazon Associate I earn from qualifying purchases.
- Catalog views expose supported metadata about database objects, columns, indexes, and other Database Engine entities. For general user-available catalog metadata, Microsoft directs customers to catalog views.
- System base tables are internal structures used by the Database Engine. Microsoft says they are not for general customer use and provides no compatibility guarantee for direct access to them.
Use the documented metadata interface rather than trying to discover or query the engine’s underlying storage structures. Microsoft also documents system procedures, T-SQL, SMO, RMO, and catalog functions as supported ways to retrieve system information where appropriate.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Which metadata interface should you use?
| Interface | Best fit | Scope and cautions |
|---|---|---|
sys catalog views |
Persistent object definitions and Database Engine catalog metadata | The general supported catalog interface; visibility depends on the caller’s ownership and permissions. |
| Dynamic management views (DMVs) | Runtime and operational state, such as active requests, sessions, or locks | Use for operational questions, not as a blanket substitute for schema and object catalog views. |
INFORMATION_SCHEMA views |
Metadata in the standardized ISO-defined scope | System-table-independent, but does not eliminate SQL Server metadata-visibility restrictions or expose every SQL Server-specific feature. |
| Feature-specific documented interfaces | Metadata for areas such as replication, backup, maintenance plans, or SQL Server Agent | Catalog views do not cover all metadata for these features; select the documented interface for the feature and SQL Server release. |
| Compatibility views | Older applications that still use SQL Server 2000-era metadata names | Backward-compatibility projections with older feature coverage and potential identifier limitations; prefer modern catalog views for new work. |
| System base tables | No general customer query or update scenario | Internal engine implementation, unsupported for direct customer access, and not covered by a compatibility guarantee. |
How do I list tables and their columns?
List tables in the current database
Use sys.tables when you want tables rather than every kind of database object:
#1 Best Overall
SELECT
s.name AS schema_name,
t.name AS table_name,
t.object_id
FROM sys.tables AS t
JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
ORDER BY s.name, t.name;The query returns tables visible to the executing principal in the current database. It does not enumerate every database on the SQL Server instance.
List columns for each table
Join sys.columns to the table metadata by object_id:
Rank #2
SELECT
s.name AS schema_name,
t.name AS table_name,
c.column_id,
c.name AS column_name,
TYPE_NAME(c.user_type_id) AS data_type,
c.max_length,
c.is_nullable
FROM sys.tables AS t
JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
JOIN sys.columns AS c
ON c.object_id = t.object_id
ORDER BY s.name, t.name, c.column_id;For a broad inventory that includes non-table objects, use sys.objects and filter by the object types you need. Choose the catalog view whose object classes and columns match the question; catalog views have base-and-derived relationships, so broadening the query unnecessarily can add irrelevant object types.
Keep production queries resilient
Avoid SELECT * against catalog views in production code. Microsoft may append columns to a catalog view in a future SQL Server release. Naming the columns your application consumes makes the query’s expected shape explicit.
What is the replacement for sysobjects and other old names?
Many familiar SQL Server 2000-era system-table names are compatibility views rather than the current metadata model. For new code, use the modern catalog views or DMVs identified for the information you need.
| Legacy name | Modern interface | How to choose |
|---|---|---|
sysobjects |
sys.objects |
General database objects; use sys.tables when the query is specifically about tables. |
syscolumns |
sys.columns |
Column metadata. |
sysdatabases |
sys.databases |
Database catalog metadata. |
sysusers |
sys.database_principals |
Database principal metadata. |
sysindexes |
sys.indexes, sys.partitions, sys.allocation_units, or sys.dm_db_partition_stats |
The appropriate replacement depends on whether the query concerns index definitions, partitions, allocation, or partition statistics. |
sysprocesses |
sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests |
Operational connection, session, and request state is split across runtime views. |
The modern model may require several views where an old name presented related information together. Compatibility views expose SQL Server 2000 metadata and do not surface metadata for features introduced in SQL Server 2005 and later. Some compatibility-view identifier columns can also return NULL or encounter arithmetic overflow at larger user or type ID ranges; use the modern catalog views designed for the wider ranges.
Rank #4
- Every application developer who uses SQL Server 2012 should own this book. To start, it presents the essential SQL statements for retrieving and updating the data in a database
Why can’t I see all tables in sys.tables?
Metadata visibility is permission-sensitive. Catalog views and metadata-emitting functions can show only securables the caller owns or has permission to access. A query returning few rows—or none—does not by itself prove that the database contains no other tables.
Check visibility with the database owner or administrator
Ask an appropriately authorized administrator to confirm whether the objects exist and what least-privilege access is appropriate. Microsoft documents VIEW DEFINITION at object, database, or server scope as a way to grant metadata visibility. The required scope and grant should be selected for the actual task, rather than granting broad server-level visibility by default.
Best Value
Account for SQL Server version
SQL Server 2022 and later also provide VIEW SECURITY DEFINITION and VIEW PERFORMANCE DEFINITION at appropriate scopes. Permission behavior and applicable syntax should be checked against documentation for the specific SQL Server release and deployment; do not assume a permission available in one release applies to an older one.
Can I update SQL Server system tables?
No. Do not attempt to change metadata by updating system base tables directly. They are internal engine structures, and direct access—including via the Dedicated Administrator Connection (DAC)—is not a supported customer scenario. Make schema changes through supported T-SQL DDL and other documented interfaces instead. Direct manipulation can bypass the engine’s supported validation and is not a safe repair method.
Release and feature scope
SQL Server documentation pages can be versioned, and the catalog and permission guidance must be applied to the reader’s actual release and deployment. Check the matching Microsoft Learn version before relying on a particular view, column, permission, or compatibility behavior. Also choose feature-specific documented interfaces for replication, backup, database maintenance plans, and SQL Server Agent, whose catalog data is outside the general catalog-view coverage described here.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsQuick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
