Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)

By Ronald R. Plew, Ryan K. Stephens

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