How to use SQ-Server System Catalogs

System catalogs are tables used by SQL Server to store information about databases, users, and security.
System catalogs are sometimes said to store meta-data because they store data that describes the data contained within

1. databases,
2. tables,
3. views,
4. stored procedures, and

other database objects. Each system catalog is a SQL Server table, which you can access by issuing a query against that table. A Database Administrator (DBA) might use system catalogs when he/she is trying to retrieve information about the number of users, locks, or other system-related information. There are over 200 system catalogs, located in various SQL Server databases, including user-created databases.

SQL Server "System Catalogs" are still used in SQL Server 2022.
However, it's important to understand how you typically interact with them in modern SQL Server versions, including 2022:
Underlying System Tables: The metadata about all aspects of a SQL Server instance and its databases (objects, configurations, users, etc.) is indeed stored in a set of underlying "system tables". These tables are part of the internal architecture of SQL Server. Their structure can sometimes change between versions. You can identify many of these tables by the `sys` prefix in their names (though not all names starting with `sys` are system tables).

• System Catalog Views: Instead of directly querying the underlying system tables, Microsoft recommends and the common practice is to use "System Catalog Views". These views are built on top of the system tables and provide a more stable and documented interface for accessing metadata.
  • Catalog views are designed to be backward-compatible, meaning they are less likely to change in a breaking way between SQL Server versions compared to the underlying system tables.
  • They present the system metadata in a more user-friendly and understandable format, often joining data from multiple system tables.
  • Examples of commonly used catalog views include:
    • sys.databases: Returns information about databases.
    • sys.tables: Returns information about user-defined tables.
    • sys.columns: Returns information about columns in tables and views.
    • sys.procedures: Returns information about stored procedures.
    • sys.views: Returns information about views.
    • sys.indexes: Returns information about indexes.
    • sys.objects: Returns information about all database objects.
    • sys.schemas: Returns information about database schemas.
    • sys.security_logins: Returns information about server-level principals.
    • sys.database_principals: Returns information about database-level principals.
• Information Schema Views: SQL Server also provides Information Schema Views. These are a set of system views that conform to the ANSI SQL standard. They provide a consistent way to query metadata across different database systems (though the level of detail might vary). Examples include INFORMATION_SCHEMA.TABLES, INFORMATION_SCHEMA.COLUMNS, etc. While they are standard, catalog views often provide more SQL Server-specific details.
• Dynamic Management Views (DMVs) and Functions (DMFs): These are another set of system objects that provide information about the current operational state of the SQL Server instance. They are used for monitoring performance, diagnosing issues, and gathering information about active processes, resource usage, etc. Examples include sys.dm_os_performance_counters, sys.dm_exec_sessions, etc. While not strictly "catalogs" in the sense of defining the structure, they are crucial for understanding the system's runtime behavior.

In summary: Yes, the underlying system catalogs (system tables) are still the foundation for storing metadata in SQL Server 2022. However, you should primarily interact with this metadata through the more stable and user-friendly System Catalog Views. Information Schema Views also exist for ANSI standard metadata queries, and Dynamic Management Views/Functions provide insights into the current operation of the server.
Think of it this way: the system tables are the internal engine, while the catalog views are the well-documented dashboard that allows you to see and understand the engine's configuration and components.

Many of the system tables are still present and utilized in SQL Server 2022. However, it is important to note that some of these tables are maintained primarily for backward compatibility and may be deprecated in future versions. Microsoft recommends using dynamic management views (DMVs) and catalog views for accessing system information in newer applications.
Backup and Restore (msdb)

• backupset: Contains a row for each backup set.
• backupmediafamily: Contains one row for each media family.
• backupmediaset: Contains one row for each backup media set.
• restorefile: Contains one row for each restored file.
• restorefilegroup: Contains one row for each restored filegroup.
• restorehistory: Contains a row for each restore operation.

Replication (distribution)

• MSdistribution_agents: Contains one row for each Distribution Agent.
• MSagent_profiles: Contains one row for each defined replication agent profile.
• MSlogreader_agents: Contains one row for each Log Reader Agent.
• sysarticleupdates: Contains one row for each article that supports immediate-updating subscriptions.

SQL Server Agent (msdb)

• sysjobs: Contains information about scheduled jobs.
• sysjobhistory: Contains information about the execution of scheduled jobs.
• sysjobsteps: Contains information about the steps of jobs.
• sysjobschedules: Contains information about job schedules.
• sysjobservers: Contains information about the servers on which jobs run

Configuration and Process Information (master)

• syscurconfigs: Contains an entry for each current configuration option.
• sysprocesses: Contains information about processes that are running on an instance of SQL Server.

⚠️ Deprecated or Compatibility-Only Tables
Some system tables, such as `sysconstraints`, are maintained for backward compatibility. Microsoft recommends using the newer catalog views and DMVs for accessing system information in modern applications. ([Microsoft Learn][12])
❌ Obsolete or Removed Tables
Certain tables, like `MSdistpublishers`, may not be present in newer versions of SQL Server or might have been replaced by updated structures. It's advisable to consult the latest SQL Server documentation or use system views to access current system information.([Microsoft Learn][12])
✅ Recommended Approach
For applications targeting SQL Server 2022, it's best to utilize the following:

• Dynamic Management Views (DMVs): Provide real-time monitoring and diagnostics.
• Catalog Views: Offer metadata about database objects.
• System Views: Expose system-level information in a consistent manner.

Relying on these views ensures compatibility with current and future versions of SQL Server.

USE { database }

-- Azure SQL Data Warehouse and Parallel Data Warehouse
USE database_name 
[;]

SQL Server Arguments:

• database: Is the name of the database or database snapshot to which the user context is switched. Database and database snapshot names must comply with the rules for identifiers.
• In Azure SQL Database, the database parameter can only refer to the current database. The USE statement does not switch between databases, and will result in
• error code 40508 is returned. To change databases, you must directly connect to the database.

You will receive an error if you query a system table that does not exist in the current database. Therefore, always change to the desired database before issuing your query against a system table.
Example: the Sysusers table:
The Sysusers table stores information about users or roles of a database. The following statement will identify which users have access to the Timesheets database.

USE Timesheets
GO
SELECT Name 
FROM sysusers
WHERE issqlrole = 0

Notice the WHERE clause, which specifies that no SQL Server role records will be returned. If you use this WHERE clause, your returned data won't be cluttered with these roles if you only want to see users. A group of users who are allowed the same access permission to certain objects. There are two main types of roles that come pre-installed in SQL Server 2012.0:..1. Fixed Server Role - allowed to perform administrative functions on specific, pre-defined categories of built-in objects.
2. Fixed Database Role - allowed to perform database-specific tasks, such as INSERTING, UPDATING, DELETING, or SELECTING data. In the next lesson, you will learn about and practice writing logic within queries.

Before moving on to the next module, click the Quiz link below to check your knowledge of batch queries and system catalogs with a short, multiple-choice quiz.
System Catalogs - Quiz