Drop All User-Defined Types from SQL Server

Question

I need to drop all User-Defined Types, User-Defined Data Types and User-Defined Tables (all in Types folder). Is it possible to do this using a T-SQL script, or must I use SSMS?

Answer 1

select  'drop type ' + quotename(schema_name(schema_id)) + '.' + quotename(name)
from sys.types
where is_user_defined = 1

You can try getting all you user defined type objects and create a script using this query or Generate Script Clicking Task under your database.

http://blog.falafel.com/t-sql-drop-all-objects-in-a-sql-server-database/

Answer 2

For the above link posted T-SQL: Drop All Objects in a SQL Server Database

I added following code to replace check Constraint from Drop Constraint by Stefan Steiger to Drop All Constraints. I chose below code because it uses same approach

DECLARE @sql nvarchar(MAX) 
SET @sql = '' 

SELECT @sql = @sql + 'ALTER TABLE ' + QUOTENAME(RC.CONSTRAINT_SCHEMA) 
+ '.' + QUOTENAME(KCU1.TABLE_NAME) 
+ ' DROP CONSTRAINT ' + QUOTENAME(rc.CONSTRAINT_NAME) + '; ' 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
 AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

Answer 3

Declare @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N' DROP type ' 
                   + QUOTENAME(SCHEMA_NAME(schema_id)) 
                   + N'.' + QUOTENAME(name)
FROM sys.types
WHERE is_user_defined = 1

Exec sp_executesql @sql