sp_iqview procedure

Function

Displays information about views in a database.

Syntax1

sp_iqview ([view_name],[view_owner],[view_type])

Syntax2

sp_iqview [view_name='viewname'],
[view_owner='viewowner' ],[view_type='viewtype' ]

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Usage

Syntax1 sp_iqview NULL,NULL,SYSTEM If you do not specify either of the first two parameters, but specify the next parameter in the sequence, you must substitute NULL for the omitted parameters. For example: sp_iqview NULL,NULL,SYSTEM and sp_iqview deptview,NULL,'ALL'.

The view_type value ALL must be enclosed in single quotes in Syntax1.

Syntax2 The parameters can be specified in any order. Enclose them in single quotes.

Table 7-67 lists the allowed values for the view_type parameter.

**Table 7-67: sp_iqview view_type values**
view_type value	Information displayed
SYSTEM	System views
ALL	User and system views
any other value	User views

Specifying one of the parameters returns only the views with the specified view name or views that are owned by the specified user. Specifying more than one parameter filters the results by all of the parameters specified. Specifying no parameters returns all user views in a database.

**Table 7-68: sp_iqview columns**
Column name	Description
view_name	The name of the view
view_owner	The owner of the view
view_def	The view definition as specified in the CREATE VIEW statement
remarks	User comments added with the COMMENT statement

sp_iqview returns a view definition greater than 32K characters without truncation.

Examples

Example 14

The following variations in syntax both return information about the view deptview:

call sp_iqview('ViewSalesOrders')

sp_iqview view_name='ViewSalesOrders'

Example 15

The following variations in syntax both return all views that are owned by view owner GROUPO:

sp_iqview NULL,GROUPO

sp_iqview view_owner='GROUPO'

view_name	view_owner	view_def	remarks
ViewSalesOrders	GROUPO	Create views GROUPO , ViewSalesOrders( ID, LineID, ProductID, Quantity, OrderDate, ShipDate, Region, SalesRepresentativeName	(NULL)