Catalog and Schema Related Views
System catalog views provide access to metadata that is stored in the system catalog of SQL Server. These views expose information about databases, tables, columns, schemas, and other database objects.
Purpose
Catalog views are essential for understanding the structure and properties of your SQL Server databases. They can be used for:
- Inspecting database objects like tables, columns, constraints, and indexes.
- Monitoring database health and performance.
- Generating scripts for database objects.
- Auditing database changes.
- Developing database administration tools.
Key Catalog Views
The following table lists some of the most commonly used catalog views related to catalog and schema information:
| View Name | Description |
|---|---|
sys.schemas |
Contains a row for each schema in a database. |
sys.objects |
Contains a row for each object that has a schema owner. |
sys.tables |
Contains a row for each table in SQL Server. User tables and system tables. |
sys.columns |
Contains a row for each column in a SQL Server database object. |
sys.types |
Contains a row for each user-defined type and system data type. |
sys.databases |
Contains a row for each database in the instance of SQL Server. |
sys.partitions |
Contains a row for each partition in the database. |
sys.objects |
Contains a row for each object that has a schema owner. |
Example Usage
Here's an example of how to retrieve all schemas and the objects within them:
SQL Query Example
SELECT
s.name AS schema_name,
o.name AS object_name,
o.type_desc
FROM
sys.schemas AS s
JOIN
sys.objects AS o ON s.schema_id = o.schema_id
WHERE
o.is_ms_shipped = 0 -- Exclude system-shipped objects
ORDER BY
schema_name, object_name;
This query will list the name of each schema and the names and types of objects (like tables, views, procedures) that belong to that schema, excluding objects created by Microsoft.
Finding Columns for a Specific Table
To find all columns for a specific table (e.g., 'Person' in the 'Sales' schema):
SQL Query Example
SELECT
c.name AS column_name,
t.name AS data_type,
c.max_length,
c.is_nullable
FROM
sys.columns AS c
JOIN
sys.types AS t ON c.user_type_id = t.user_type_id
JOIN
sys.objects AS o ON c.object_id = o.object_id
JOIN
sys.schemas AS s ON o.schema_id = s.schema_id
WHERE
s.name = 'Sales' AND o.name = 'Person'
ORDER BY
c.column_id;