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

None required.

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:

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

Table 10-9: 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 rowid’, dbo, ALL

No rows returned, as the data type rowid is owned by SYS

See also

“SYSUSERTYPE system table”

“SYSDOMAIN system table”

CREATE DOMAIN statement

Chapter 4, “SQL 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:

Table 10-10: 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)