Show all User-Defined Datatypes for all Databases

USE MASTER
GO

CREATE PROCEDURE sp_ShowAllUserTypes

AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ISNULL(@sql + char(13) + 'UNION' + 
CHAR(13), '') +
'SELECT ''' + name + ''' as db, *
FROM ' + name + '.dbo.systypes
WHERE xusertype > 256'
/*User-defined types always have an xusertype greater than 256*/FROM master.dbo.sysdatabases

--PRINT @sql /*Uncomment for debugging*/EXEC sp_executesql @sql