sp_iqdatatype procedure

Function

Displays information about system data types and user-defined data types.

Syntax

sp_iqdatatype [ type-name ], [ type-owner ], [ type-type ]

Permissions

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

Usage

type-name The name of the data type.

type-owner The name of the creator of the data type.

type-type The type of data type. Allowed values are:

SYSTEM: displays information about system defined data types (data types owned by user SYS or dbo) only
ALL: displays information about user and system data types
Any other value: displays information about user data types

The sp_iqdatatype procedure can be invoked without any parameters. If no parameters are specified, only information about user-defined data types (data types not owned by dbo or SYS) is displayed by default.

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_iqdatatype NULL, NULL, SYSTEM and sp_iqdatatype NULL, user1.

**Table 7-13: sp_iqdatatype usage examples**
Syntax	Output
sp_iqdatatype	Displays information about all user-defined data types in the database
sp_iqdatatype address	Displays information about the user-defined data type named address
sp_iqdatatype non_existing_type	No rows returned, as the data type non_existing_type does not exist
sp_iqdatatype NULL, DBA	Displays information about all user-defined data types owned by DBA
sp_iqdatatype address, DBA	Displays information about the data type address owned by DBA
sp_iqdatatype rowid	rowid is a system-defined data type. If there is no user-defined data type also named rowid, no rows are returned. (By default, only user-defined data types are returned.)
sp_iqdatatype rowid, SYS	No rows returned, as the data type rowid is not a user-defined data type (by default, only user-defined data types are returned)
sp_iqdatatype NULL, NULL, SYSTEM	Displays information about all system defined data types (owned by dbo or SYS)
sp_iqdatatype rowid, NULL, SYSTEM	Displays information about the system data type rowid
sp_iqdatatype NULL, NULL, 'ALL'	Displays information about the rowid data type

Description

The sp_iqdatatype stored procedure displays information about system and user-defined data types in a database. User-defined data types are also referred to as domains. Predefined domain names are not included in the sp_iqdatatype output.

If you specify one or more parameters, the sp_iqdatatype result is filtered by the specified parameters. For example, if type-name is specified, only information about the specified data type is displayed. If type-owner is specified, sp_iqdatatype only returns information about data types owned by the specified owner. If no parameters are specified, sp_iqdatatype displays information about all the user-defined data types in the database.

The sp_iqdatatype procedure returns information in the following columns:

**Table 7-14: sp_iqdatatype columns**
Column name	Description
type_name	The name of the data type
creator	The owner of the data type
nulls	Y indicates the user-defined data type allows nulls; N indicates the data type does not allow nulls.
width	Displays the length of string columns, the precision of numeric columns, and the number of bytes of storage for all other data types
scale	Displays the number of digits after the decimal point for numeric data type columns and zero for all other data types
“default”	The default value for the data type
“check”	The CHECK condition for the data type

Example

Display information about the user-defined data type address:

sp_iqdatatype address

type_name    creator    nulls    width    scale    “default”    “check”
address      DBA        Y        5        0         (NULL)       (NULL)