[SOLVED] Delete all service broker message types

Delete all service broker message types

Currently I am using:

SELECT * FROM sys.service_message_types

to list service broker message types. However, how would I go about deleting all message types?

Solution

Below is an example that drops all user message types in the current database, using the model database as a reference to exclude system message types.

Note the DROP will fail if contracts use the type. Contracts bound to the type and indirectly referencing SB objects (i.e. services) will need to be dropped first.

DECLARE @SQL nvarchar(MAX);

SELECT @SQL = STRING_AGG(N'DROP MESSAGE TYPE ' + QUOTENAME(current_db.name), ';')
FROM sys.service_message_types AS current_db
LEFT JOIN model.sys.service_message_types AS model_db ON
     model_db.message_type_id = current_db.message_type_id
WHERE model_db.message_type_id IS NULL;

EXECUTE sp_executesql @SQL;