| Lesson 4 | System catalogs |
| Objective | Describe How to use SQL Server System Catalogs |
How to use SQL Server System Catalogs
What are 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
- databases,
- tables,
- views,
- 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.
- 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.
- 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 Slideshow:
Dynamic SQL
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.
System Catalogs Quiz
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