Catalog Views

Catalog views contains info for metadata for each user table.
Catalog views belong to the sys schema, so you have to use the schema name when you access one 
of the objects. 

This section describes some of the most important catalog views.

Applies to: SQL Server, Azure SQL Database, Azure Synapse Analytics 

Views

sys.schemas

Contains info for each database schema.

Columns:

1. name: Name of the schema. Unique within the database
2. schema_id(int): ID number of the schema. Unique within the database

sys.tables

Contains info for each user-table in SQL server.

Columns:

1. name: Table Name
2. object_id(int): Object id number. Is a unique number for each table in the database.
3. schema_id(int): ID number to identify which schema the table belongs.

 sys.columns

Contains info for each column of a table.

Columns:

1. object_id(int): ID  number to identify which table this column belongs.
2. system_type_id(tinyint): ID of the system type of the column.
3. name: Name of the column
4. column_id(int): ID of the column
5. is_nullable(bit):         1 = Column is nullable. 
6. max_length(smallint): in bytes, the maximum length  of the column.

sys.types

Contains info for each system data type.

Columns:

1. name:        Name of the type
2. max_length(smallint):        in bytes, the maximum length  of the type.
3. system_type_id(tinyint):        ID of the internal system-type of the type.
4. user_type_id(int):        ID of the type. 

Environment 

SQL Server  2019 Developer Edition.

SQL Server Management Studio v18.10

Database: DWREAD

Schema: FORMULA_STAG

Tables:

 

The tables are  from Kuggle Formula 1 World Championship (1950 - 2020)     👈

Querying Catalog Views

SELECT     ROW_NUMBER() OVER(ORDER BY (select 1) ) as [Id], 
           sch.name as [schema],
           tbl.name as [table],
           col.name as [column],
           col.column_id as [column order],
           col.is_nullable as [is nullable],
           col.max_length as [length],
           tp.name as [type],
           tp.max_length as [max length]
FROM sys.tables tbl
JOIN sys.columns col
ON ( tbl.object_id = col.object_id )
JOIN sys.types tp
ON ( col.system_type_id = tp.system_type_id AND tp.system_type_id = tp.user_type_id) 
JOIN sys.schemas sch
ON ( sch.schema_id = tbl.schema_id )
WHERE sch.schema_id in (5)
ORDER BY [table], [column order]

The result set  of the above query is the following: