Querying the System Catalog | Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)


Team-Fly

	Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
	Table of Contents

	Hour 21. Working with the System Catalog

The system catalog tables or views are queried as any other table or view in the database using SQL. A user can usually query the user - related tables, but may be denied access to various system tables that can be accessed only by privileged database user accounts, such as the database administrator.

You create a SQL query to retrieve data from the system catalog just as you create a query to access any other table in the database.

For example, the following query returns all rows of data from the Sybase table SYSTABLES:

 SELECT * FROM SYSTABLES  GO

The following section displays a few examples of querying system catalog tables and some of the information that you may stumble across.

Examples of System Catalog Queries

The following examples use Oracle's system catalog. Oracle is chosen for no particular reason other than that it is the implementation with which this book's authors are most familiar.

The following query lists all user accounts in the database:

    SELECT USERNAME   FROM ALL_USERS;   USERNAME ---------------- SYS SYSTEM RYAN SCOTT DEMO RON USER1 USER2 8 rows selected.

The following query lists all tables owned by a user:

    SELECT TABLE_NAME   FROM USER_TABLES;   TABLE_NAME ---------------- CANDY_TBL CUSTOMER_TBL EMPLOYEE_PAY_TBL EMPLOYEE_TBL PRODUCTS_TBL ORDERS_TBL 6 rows selected.

The next query returns all the system privileges that have been granted to the database user BRANDON:

    SELECT GRANTEE, PRIVILEGE   FROM SYS.DBA_SYS_PRIVS   WHERE GRANTEE = 'BRANDON';   GRANTEE                       PRIVILEGE ---------------------- -------------------- BRANDON                ALTER ANY TABLE BRANDON                ALTER USER BRANDON                CREATE USER BRANDON                DROP ANY TABLE BRANDON                SELECT ANY TABLE BRANDON                UNLIMITED TABLESPACE 6 rows selected.

The following is an example from MS Access:

    SELECT NAME   FROM MSYSOBJECTS   WHERE NAME = 'MSYSOBJECTS'   NAME ----------- MSYSOBJECTS

The examples shown in this section are a drop in the bucket compared to the information that you can retrieve from any system catalog. You may find it to be extremely helpful to dump data dictionary information using queries to a file that can be printed and used as a reference. Please refer to your implementation documentation for specific system catalog tables and columns within those available tables.


Team-Fly

Top