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.

NoteThe 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-58 lists the allowed values for the table_type parameter:

Table 7-58: 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

Description

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-59: sp_iqtable columns

Column name

Description

table_name

The name of the table.

table_type

BASE – a base table.

MAT VIEW - a materialized view.

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)