Skip to content

Latest commit

 

History

History
69 lines (52 loc) · 3.48 KB

drop-type-transact-sql.md

File metadata and controls

69 lines (52 loc) · 3.48 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
DROP TYPE (Transact-SQL)
DROP TYPE (Transact-SQL)
WilliamDAssafMSFT
wiassaf
05/12/2017
sql
t-sql
reference
DROP TYPE
DROP_TYPE_TSQL
user-defined types [SQL Server], deleting
UDTs [SQL Server], deleting
alias data types [SQL Server], removing
DROP TYPE statement
TSQL

DROP TYPE (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

Removes an alias data type or a common language runtime (CLR) user-defined type from the current database.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

DROP TYPE [ IF EXISTS ] [ schema_name. ] type_name [ ; ]

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

IF EXISTS
Applies to: [!INCLUDEssNoVersion] ( [!INCLUDEsssql16-md] through current version).

Conditionally drops the type only if it already exists.

schema_name
Is the name of the schema to which the alias or user-defined type belongs.

type_name
Is the name of the alias data type or the user-defined type you want to drop.

Remarks

The DROP TYPE statement will not execute when any of the following is true:

  • There are tables in the database that contain columns of the alias data type or the user-defined type. Information about alias or user-defined type columns can be obtained by querying the sys.columns or sys.column_type_usages catalog views.

  • There are computed columns, CHECK constraints, schema-bound views, and schema-bound functions whose definitions reference the alias or user-defined type. Information about these references can be obtained by querying the sys.sql_expression_dependencies catalog view.

  • There are functions, stored procedures, or triggers created in the database, and these routines use variables and parameters of the alias or user-defined type. Information about alias or user-defined type parameters can be obtained by querying the sys.parameters or sys.parameter_type_usages catalog views.

Permissions

Requires either CONTROL permission on type_name or ALTER permission on schema_name.

Examples

The following example assumes a type named ssn is already created in the current database.

DROP TYPE ssn ;

See Also

CREATE TYPE (Transact-SQL)
EVENTDATA (Transact-SQL)