sp_iqdatatype Procedure

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

Parameters
Parameter	Description
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.

sp_iqdatatype usage examples
Syntax	Output
sp_iqdatatype	Displays information about all user-defined data types in the database
sp_iqdatatype country_t	Displays information about the user-defined data type named `country_t`
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 country_t, DBA	Displays information about the data type `country_t` 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 user-defined and system data types

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:

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 and U indicates the null value for the data type is unspecified.
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 country_t:

sp_iqdatatype country_t

type_name    creator    nulls    width    scale    “default”    “check”
country_t    DBA        U       15        0         (NULL)       (NULL)