Standard View in SQL Server

 In SQL Server, "standard views" often refer to system views that provide information about the state of the SQL Server instance, databases, and their objects. These views are part of the system catalog and are useful for querying metadata. Here are some commonly used standard views in SQL Server:

1. sys.tables: Provides information about tables in the current database.
2. sys.columns: Gives details about columns in the tables.
3. sys.indexes: Contains information about indexes on tables and views.
4. sys.objects: Provides information about schema-scoped objects such as tables, views, procedures, and functions.
5. sys.views: Lists the views in the current database.
6. sys.procedures: Shows stored procedures in the current database.
7. sys.dm_exec_sessions: Provides information about active user sessions.
8. sys.dm_exec_requests: Displays information about requests currently executing.

Here's a simple example of using a standard view in SQL Server to get information about tables in a database:

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

In this query:

• sys.tables is the standard system view that provides information about tables in the current database.
• name gives the name of the table.
• create_date shows when the table was created.
• modify_date indicates the last time the table was modified.

This query will return a list of all tables in the database, along with their creation and modification dates.

If you want more detailed information about the columns in each table, you can join sys.tables with sys.columns:

SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    c.column_id AS ColumnID,
    c.system_type_id AS DataTypeID,
    ty.name AS DataTypeName,
    c.max_length AS MaxLength,
    c.is_nullable AS IsNullable
FROM 
    sys.tables t
JOIN 
    sys.columns c ON t.object_id = c.object_id
JOIN 
    sys.types ty ON c.user_type_id = ty.user_type_id
ORDER BY 
    t.name, c.column_id;

In this query:

• sys.columns provides details about columns.
• sys.types is joined to get the human-readable data type name.
• The result includes the table name, column name, column ID, data type ID, data type name, maximum length, and whether the column is nullable.