sp_iqtable procedure

Function

Lists tables and information about them.

Syntax1

sp_iqtable ( [ table_name ],[table_owner ],[table_type ] )

Syntax2

sp_iqtable [table_name='tablename' ],
[table_owner='tableowner' ],[table_type='tabletype' ]

Usage

Syntax1 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 parameter(s). For example, sp_iqtable NULL,NULL,TEMP and sp_iqtable NULL,dbo,SYSTEM.

The table_type values ALL and VIEW must be enclosed in single quotes in Syntax1.

Syntax2 The parameters can be specified in any order. Be sure to enclose them in single quotes.

Table 9-20 lists the allowed values for the table_type parameter.

**Table 9-20: sp_iqtable table_type values**
table_type value	information displayed
SYSTEM	system tables
TEMP	global temporary tables
VIEW	views
ALL	IQ tables, system tables, and views
any other value	IQ tables

Displays information about tables in the database. Specifying one of the parameters returns only the tables that match that parameter. Specifying more than one parameter filters the results by all of the parameters specified. Specifying no parameters returns all IQ tables in the database. There is no method for returning the names of local temporary tables.

**Table 9-21: sp_iqtable columns**
Column name	Description
table_name	The name of the table
table_type	SYSTEM, BASE = a base table VIEW = a view TEMP, JVT = all join virtual tables of both IQ Store and IQ Local Stores, ALL, GBL = a global temporary table, JVT_MAIN = join virtual table of the IQ Store, JVT_LOCAL = join virtual table of an IQ Local Store.
table_owner	The owner of the table
server_type	IQ = an object created in the IQ Store SA = an object created in the SA Store Note that all views are created in the SA store.
location	TEMP = IQ Temp Store, MAIN = IQ Store, LOCAL = IQ Local Store, SYSTEM = Catalog Store
remarks	User comments added with the COMMENT statement

Examples

Example 8

The following variations in syntax both return information about the table department:

call sp_iqtable ('department')

sp_iqtable table_name='department'

table_name	table_type	table_owner	server_type	location	remarks
department	BASE	DBA	IQ	MAIN	(NULL)

Example 9

The following variations in syntax both return all tables that are owned by table owner DBA:

sp_iqtable NULL,DBA

sp_iqtable table_owner='DBA'

table_name	table_type	table_owner	server_type	location	remarks
contact	BASE	DBA	IQ	MAIN	(NULL)
customer	BASE	DBA	IQ	MAIN	(NULL)
department	BASE	DBA	IQ	MAIN	(NULL)
employee	BASE	DBA	IQ	MAIN	(NULL)
fin_code	BASE	DBA	IQ	MAIN	(NULL)
fin_data	BASE	DBA	IQ	MAIN	(NULL)
product	BASE	DBA	IQ	MAIN	(NULL)
sales_order	BASE	DBA	IQ	MAIN	(NULL)
sales_order_items	BASE	DBA	IQ	MAIN	(NULL)