SQL Server Table Reference

SQL Server System Tables and Views

This section provides reference information for system tables and system views in SQL Server. These objects contain metadata about the SQL Server instance, its databases, and the objects within them.

Understanding System Objects

System objects are essential for database administration, development, and troubleshooting. They are typically prefixed with sys or INFORMATION_SCHEMA. You can query these objects using Transact-SQL (T-SQL) to retrieve information about your database environment.

Key Categories:

System Catalog Views (sys.): Provide detailed metadata about SQL Server objects. These views are the recommended way to access metadata.
Information Schema Views (INFORMATION_SCHEMA.): Standard SQL views that provide information about database objects and their properties.
System Functions: Built-in functions that return information about the system or its objects.

Quick Links

sys.tables
sys.columns
sys.objects
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS

`sys.tables`

Represents tables in SQL Server. This view provides information about user-defined tables and system tables.

SELECT
    name AS TableName,
    object_id,
    create_date,
    modify_date
FROM
    sys.tables;

`sys.columns`

Represents columns in SQL Server objects, including tables, views, and table-valued functions.

SELECT
    c.name AS ColumnName,
    t.name AS TableName,
    c.max_length,
    c.precision,
    c.scale
FROM
    sys.columns AS c
JOIN
    sys.tables AS t ON c.object_id = t.object_id
WHERE
    t.name = 'YourTableName'; -- Replace with your table name

`sys.objects`

Contains a row for each schema-scoped object that is created within an instance of SQL Server.

SELECT
    name,
    type_desc,
    create_date,
    modify_date
FROM
    sys.objects
WHERE
    type = 'U'; -- 'U' for User Table

`INFORMATION_SCHEMA.TABLES`

Returns information about tables in the current database. It includes user tables and system tables.

SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_TYPE
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_TYPE = 'BASE TABLE';

`INFORMATION_SCHEMA.COLUMNS`

Returns information about columns in the current database.

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    NUMERIC_PRECISION,
    NUMERIC_SCALE
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'YourTableName'; -- Replace with your table name

Additional Resources

For more in-depth information, refer to the official Microsoft SQL Server documentation: