sp_iqtable procedure

Function

Displays information about tables in the database.

Syntax1

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

Syntax2

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

Permissions

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

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 parameters. 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. Enclose them in single quotes.

Table 7-60 lists the allowed values for the table_type parameter:

**Table 7-60: 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

Specifying one parameter 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 Sybase IQ tables in the database. There is no method for returning the names of local temporary tables.

**Table 7-61: sp_iqtable columns**
Column name	Description
table_name	The name of the table.
table_type	BASE – a base table. MAT VIEW - a materialized view. (SA tables only) GBL TEMP - a global temporary table. PARTITION - a table partition (this table is for internal use only and cannot be used by Sybase IQ users). VIEW – a view. JVT – a join virtual table.
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. All views are created in the SA store.
location	TEMP – IQ temporary store. MAIN – IQ store. SYSTEM – catalog store.
dbspace_id	Name of the dbspace where the table resides.
isPartitioned	'Y' if the column belongs to a partitioned table and has one or more partitions whose dbspace is different from the table partition’s dbspace, 'N' if the column’s table is not partitioned or each partition of the column resides in the same dbspace as the table partition.
remarks	User comments added with the COMMENT statement.
table_constraints	Constraints against the table.

Examples

Example 12

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

sp_iqtable ('Departments')

sp_iqtable table_name='Departments'

Table_name	Table_type	Table_owner	Server_type	location
Departments	BASE	GROUPO	IQ	Main

dbspace_id	isPartitioned	Remarks	table_constraints
16387	N	(NULL)	(NULL)

Example 13

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

sp_iqtable NULL,GROUPO

sp_iqtable table_owner='GROUPO'

Table_name	Table_type	Table_owner	Server_type	location
Contacts	BASE	GROUPO	IQ	Main
Customers	BASE	GROUPO	IQ	Main
Departments	BASE	GROUPO	IQ	Main
Employees	BASE	GROUPO	IQ	Main
FinancialCodes	BASE	GROUPO	IQ	Main
FinancialData	BASE	GROUPO	IQ	Main
Products	BASE	GROUPO	IQ	Main
SalesOrders	BASE	GROUPO	IQ	Main
SalesOrderItems	BASE	GROUPO	IQ	Main

dbspace_id	isPartitioned	Remarks	table_constraints
16387	N	(NULL)	(NULL)
16387	N	(NULL)	(NULL)
16387	N	(NULL)	(NULL)
16387	N	(NULL)	(NULL)
16387	N	(NULL)	(NULL)
16387	N	(NULL)	(NULL)
16387	N	(NULL)	(NULL)
16387	N	(NULL)	(NULL)
16387	N	(NULL)	(NULL)