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.

Depending on the type of information being stored, the system table might reside in only the

1. master database,
2. msdb database,
3. database used for distribution,
4. replication,
5. subscription, or
6. a user-created database.

The table below lists all of the system tables, their location, and a brief description of the table.
Note that you may never need many of these tables, but it is a good idea to read through the table to understand the breadth, depth, and complexity of the information stored in SQL Server. During the course of your career using SQL Server, you will need to access some of these tables.

1. Meta-data: Information (data) about database structures. For example, the meta-data about a table includes its name, its column names, and the filegroups in which it resides. In other words, meta-data is data about data.
2. System catalogs: Also known as system tables, these tables store meta-data, which is data that describes other data.

System catalogs are also referred to as system tables.
Example: The Sysobjects table
The Sysobjects table stores all objects in a database. If you wanted to identify the user-defined tables in your Timesheets database, you could issue the statement shown in the following series of images:

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