Database Engine Views Reference

This section provides detailed reference information for the system views available in the SQL Server Database Engine. System views provide metadata about the Database Engine instance, its databases, and objects within those databases.

System Catalog Views

System catalog views expose information about a SQL Server instance. They are organized into categories for easier navigation.

Information Schema Views

Information Schema views provide a standardized way to query metadata about database objects, adhering to the SQL standard.

• INFORMATION_SCHEMA.TABLES
• INFORMATION_SCHEMA.COLUMNS
• INFORMATION_SCHEMA.VIEWS
• INFORMATION_SCHEMA.SCHEMATA
• INFORMATION_SCHEMA.KEY_COLUMN_USAGE
• INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Dynamic Management Views (DMVs)

DMVs provide real-time operational state information about the SQL Server instance. They are essential for performance monitoring and troubleshooting.

• sys.dm_exec_sessions
• sys.dm_exec_connections
• sys.dm_db_stats_properties
• sys.dm_os_wait_stats
• sys.dm_io_virtual_file_stats

System Catalog Views (sys schema)

These views provide detailed metadata about SQL Server objects, configuration, and security.

• sys.objects
• sys.columns
• sys.databases
• sys.tables
• sys.views
• sys.schemas
• sys.procedures

INFORMATION_SCHEMA.TABLES

Returns a list of tables and views in the current database.

SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_TYPE
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_TYPE IN ('BASE TABLE', 'VIEW');
                        

INFORMATION_SCHEMA.COLUMNS

Returns information about columns in tables and views.

SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    ORDINAL_POSITION,
    DATA_TYPE,
    IS_NULLABLE
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'YourTableName';
                        

INFORMATION_SCHEMA.VIEWS

Returns information about views in the current database.

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    VIEW_DEFINITION
FROM
    INFORMATION_SCHEMA.VIEWS;
                        

sys.dm_exec_sessions

Returns information about all active user sessions and system sessions running on SQL Server.

SELECT
    session_id,
    login_name,
    host_name,
    program_name,
    login_time,
    status
FROM
    sys.dm_exec_sessions;
                        

sys.objects

Contains a row for each schema-scoped object within SQL Server.

SELECT
    name,
    object_id,
    schema_id,
    type_desc,
    create_date,
    modify_date
FROM
    sys.objects
WHERE
    type = 'V'; -- 'V' for View
                        

sys.views

Contains a row for each view in SQL Server.

SELECT
    name,
    object_id,
    schema_id,
    create_date,
    modify_date
FROM
    sys.views;
                        

Explore the various system views to gain insights into your SQL Server environment. For comprehensive details, refer to the official Microsoft documentation.