Lesson 3: Understanding and Querying System and Database Catalogs | MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228

3 4

SQL Server 2000 uses a variety of system tables to manage an instance of SQL Server 2000 and its associated databases. These tables hold meta data about the system as a whole and about objects in each database. This lesson covers the most frequently used system tables, including the use of system stored procedures, system functions, and Information Schema Views to query these tables.

After this lesson, you will be able to

Understand the most frequently used system tables
Use system stored procedures to query system tables
Use system functions to query system tables
Use Information Schema Views to query system tables

Estimated lesson time: 15 minutes

Introducing System Tables

The system tables used by SQL Server 2000 consist of two groups of tables: the system catalog and the database catalog. Changing or deleting a system table can cause SQL Server 2000 to lose functionality, function erratically, or be unable to function at all.

Note

System tables begin with the sys prefix.

System Catalog

The system catalog consists of the set of system tables SQL Server 2000 uses to manage the entire instance, and exists only in the master database. These system tables record meta data about the entire instance (such as all users and all databases) and system configuration settings (such as server configuration settings).

Table 5.1 describes some of the most frequently queried system tables in the system catalog.

Table 5.1 Frequently queried system tables in the system catalog

System Table	Description
Sysaltfiles	Contains a row of information for each file in the database, including the file ID, database ID (dbid), physical and logical filenames, location, size, and growth characteristics.
Sysconfigures	Contains a row of information for each server option set by an administrator before SQL Server 2000 started, plus dynamic configuration options set since startup.
Sysdatabases	Contains a row of information for each database, including the dbid, security identifier (SID) of the database owner, creation date, database compatibility level, location of the primary file, and database configuration options that have been set.
Sysdevices	Contains a row of information for each permanent backup device that has been created, including the physical and logical filenames, file size, and controller type for the device (such as disk or tape).
Syslockinfo	Contains a row of information for each waiting, converting, and granted lock request, including the ID of the user or process requesting the lock and the object being locked.
Syslogins	Contains a row of information for each login account, including the sid , login ID, encrypted password (may be NULL), default database, and server role.
Sysmessages	Contains a row of information for each system error or warning that SQL Server 2000 can return, including the error number, severity level, and description.
Sysperfinfo	Contains a row of information for each SQL Server performance counter, including the object name, counter name, and counter value. These counters are used in Windows System Monitor (or Windows 2000 Performance Monitor) and performance condition alerts.

Database Catalog

The database catalog consists of a set of system tables used to manage a particular database. Each database has a set of these system tables. The system tables contain sufficient information for a user database to be detached from one instance of SQL Server 2000 and attached to another instance with the same or different database name. Table 5.2 describes some of the most frequently queried system tables in the database catalog.

Table 5.2 Frequently queried system tables in the database catalog

System Table	Description
Syscomments	Contains a row of information for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The text column contains the original Transact-SQL definition statement used to create the object. None of the entries in this table should be modified or removed. You can hide stored procedure definitions by using the ENCRYPTION keyword when the stored procedure is created.
Sysindexes	Contains a row of information for each index and table in the database, including the index ID (indid), type, original fill factor, and index name.
Sysobjects	Contains a row of information for each object in a database, including object name, object ID, user ID (uid) of the object owner, and creation date.
Sysusers	Contains a row of information for each Windows user, Windows group, SQL Server user, and SQL Server role in the database, including the user ID, username, group ID (gid), and creation date.

Retrieving System Information

You can query system tables directly, but querying system tables by using scripts is not recommended because Microsoft might change system tables in future releases to add new functionality. These changes in a new release of SQL Server could break any scripts that query system tables directly. SQL Server 2000 provides a number of mechanisms to query system tables that you can embed in scripts that will not be affected by future changes to system tables. These mechanisms include system stored procedures, system functions, and information schema views.

Note

System tables can also be queried using OLE DB schema rowsets and ODBC catalog functions.

Practice: Querying System Tables Directly

In this practice you use SQL Query Analyzer to query system tables directly.

To query system tables directly

Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
SQL Query Analyzer appears displaying the Connect To SQL Server dialog box.
Connect to your default instance using Windows authentication.
SQL Query Analyzer appears displaying a new query window.
In the query pane, type SELECT * FROM sysdatabases.
On the toolbar, click the Execute Query button, or press the F5 key, or press Ctrl-E to execute the query.
Notice that the results pane displays information regarding each database in this instance of SQL Server 2000.
On the toolbar, click the Clear Window button or press Ctrl+Shift+Del simultaneously.
In the query pane, type SELECT * FROM sysaltfiles.
Notice that the results pane displays information regarding each data and transaction log file used by this instance of SQL Server 2000.
Close SQL Query Analyzer.
Click the No button if a SQL Query Analyzer dialog box appears asking if you want to save the changes.

System Stored Procedures

System stored procedures are prewritten Transact-SQL statements that ship with SQL Server 2000. System stored procedures work with system tables to provide system information to and perform administrative tasks for database administrators.

Note

System stored procedures begin with an sp_.

Table 5.3 describes some of the most frequently used system stored procedures for querying system tables.

Practice: Querying System Tables Using System Stored Procedures

In this practice you use system stored procedures in SQL Query Analyzer to query system tables.

To query system tables using system stored procedures

Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
SQL Query Analyzer appears displaying the Connect To SQL Server dialog box.
Connect to your default instance using Windows authentication.
SQL Query Analyzer appears displaying a new query window.
In the query pane, type sp_helpdb.
On the toolbar, click the Execute Query button.
Notice that the results pane displays information regarding each database in this instance of SQL Server 2000.
In the query pane, type a space and then Northwind to modify your query to read sp_helpdb Northwind.
Notice that the results pane displays information regarding the Northwind database only, including an additional result set showing the file allocation for the Northwind database.
On the toolbar, click the Clear Window button.
In the query pane, type sp_spaceused.
On the toolbar, click the Execute Query button.
Notice that the results pane displays information regarding space used by the current database, which is master.
On the toolbar, click the drop-down list and then change the current database to Northwind.
On the toolbar, click the Execute Query button.
Notice that the results pane displays information regarding space used by the current database, which is now Northwind.
In the query pane, type 'customers' to modify your query to read sp_spaceused 'customers'.
On the toolbar, click the Execute Query button.
Notice that the results pane now displays information regarding space used by the customers table in the Northwind database.
Close SQL Query Analyzer.
Click the No button if a SQL Query Analyzer dialog box appears asking whether you want to save the changes.

Table 5.3 System stored procedures used for querying system tables

System Stored Procedure	Description
Sp_configure ['name', 'value']	Reports (or changes) configuration settings for a SQL Server 2000 instance.
Sp_dboption ['database', 'option', 'value']	Reports (or changes) database options for a particular database.
Sp_help ['object']	Reports information about a particular database object or data type.
Sp_depends ['object']	Reports information about dependencies of database objects, such as views or procedures that depend on a particular table.
Sp_helpdb ['database']	Reports information about a particular database (or all databases if no database is specified).
Sp_helpfile ['name']	Reports the physical names and attributes of files associated with the current database.
Sp_lock ['spid1', 'spid2']	Reports information about current locks.
Sp_monitor	Reports information about how busy SQL Server 2000 has been since it started and since sp_monitor was last run.
Sp_spaceused ['object', 'updateusage' ]	Reports information about the number of rows, disk space reserved, disk space used by a table or database, and whether the DBCC UPDATEUSAGE command should be run.
Sp_statistics ['table_name', 'owner', 'qualifier', 'index_name', 'is_unique', 'accuracy']	Reports information about all indexes and statistics on a table or view.
Sp_who ['login']	Reports information about current SQL Server 2000 users and processes, and can be filtered using the ACTIVE keyword to display only processes that are not idle.

System Functions

System functions are a set of built-in functions that ship with SQL Server 2000 that query system tables from within Transact-SQL statements and return specific information about values, objects, and settings. Table 5.4 describes some of the system functions used most frequently by database administrators to query system tables.

Table 5.4 System functions used to query system tables

System Function	Description
DATABASEPROPERTYEX ('database', 'property')	Returns a value regarding a database option or property (such as Recovery).
DB_ID ('database')	Returns the ID number of a database.
DB_NAME (database_id)	Returns the name of a database.
FILE_ID ('file_name')	Returns the ID number of a logical filename.
FILE_NAME (file_ID)	Returns the logical file name of a file.
FILEPROPERTY ('file_name', 'property')	Returns a value regarding a file property (such as SpaceUsed).
GET DATE ()	Returns the current system date and time in the SQL Server 2000 format for datetime values.
HOST_NAME ()	Returns the name of the host computer.
STATS_DATE (table_id, index_id)	Returns the date that statistics for an index were updated.
USER_ID ('user_name')	Returns the database ID of a user.
USER_NAME (id)	Returns the database name of a user (such as dbo).

Practice: Querying System Tables Using System Functions

In this practice you use system functions in SQL Query Analyzer to query system tables.

To query system tables using system functions

Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
SQL Query Analyzer appears displaying the Connect To SQL Server dialog box.
Connect to your default instance using Windows authentication.
SQL Query Analyzer appears displaying a new query window.
In the query pane, type SELECT DB_ID ('Northwind')
On the toolbar, click the Execute Query button.
Notice that the results pane displays the database ID for the Northwind database in this instance of SQL Server 2000.
On the toolbar, click the Clear Window button.
In the query pane, type SELECT FILEPROPERTY ('Northwind', 'SpacedUsed')
On the toolbar, click the Execute Query button.
Notice that the results pane displays the number of pages allocated in the Northwind database.
On the toolbar, click the Clear Window button.
In the query pane, type SELECT USER_NAME (2).
Notice that the results pane displays the name for the user with a user ID of 2 (this is the Guest account).
Close SQL Query Analyzer.
Click the No button if a SQL Query Analyzer dialog box appears asking whether you want to save the changes.

Information Schema Views

Information schema views are views of system and database catalog information based on the ANSI SQL-92 standards. These views are independent of the implementation of catalogs by any particular vendor, and thus applications using information schema views are portable between heterogeneous systems that comply with ANSI SQL-92. They are also independent of any changes to the underlying system tables.

Table 5.5 describes some of the most frequently used information schema views for querying system tables.

Table 5.5 Information schema views for querying system tables

Information Schema View	Description
Information_schema.columns	Contains a row of information for each column accessible to the current user in the current database.
Information_schema.schemata	Contains a row of information for each database in which the current user has permissions.
Information_schema.tables	Contains a row of information for each table in the current database in which the current user has permissions.
Information_schema.table_privileges	Contains a row of information for each table privilege granted to or by the current user in the current database.
Information_schema.view_table_usage	Contains a row of information for each table in the current database used in a view.

Practice: Querying System Tables Using Information Schema Views

In this practice you use information schema views in SQL Query Analyzer to query system tables.

To query system tables using information schema views

Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
SQL Query Analyzer appears displaying the Connect To SQL Server dialog box.
Connect to your default instance using Windows authentication.
SQL Query Analyzer appears displaying a new query window.
On the toolbar, click the drop-down list and then change the current database to Northwind.
In the query pane, type SELECT * FROM INFORMATION_SCHEMA.SCHEMATA.
On the toolbar, click the Execute Query button.
Notice that the results pane displays information regarding all databases in this instance of SQL Server 2000.
On the toolbar, click the Clear Window button.
In the query pane, type SELECT * FROM INFORMATION _SCHEMA.TABLES.
On the toolbar, click the Execute Query button.
Notice that the results pane displays information regarding tables in the current database.
On the toolbar, click the Clear Window button.
In the query pane, type SELECT * FROM INFORMATION _SCHEMA.TABLE_PRIVILEGES.
On the toolbar, click the Execute Query button.
Notice that the results pane displays information regarding privileges on all tables in the Northwind database.
Close SQL Query Analyzer.
Click the No button if a SQL Query Analyzer dialog box appears asking whether you want to save the changes.

Lesson Summary

The system catalog and database catalog contain system tables used by SQL Server 2000 to manage the entire instance and each particular database. Although you can query these system tables directly using SQL Query Analyzer, you should not embed direct queries into Transact-SQL scripts because the system tables might change in future releases of SQL Server. If you want to embed queries in Transact-SQL scripts, you should use one of several methods that are guaranteed to work with future versions of SQL Server. These include system stored procedures, system functions, and information schema views.