To Identify User Defined Data Types and Used Tables

We can identify the Userdefined data types using Sys.Types DMV.

Creating User defined data type(s):

Use SQLServerBuddy
Go

CREATE TYPE Gender FROM VARCHAR(1) NOT NULL
Go
CREATE TYPE Age FROM TINYINT NOT NULL
Go
CREATE TYPE Name FROM VARCHAR(50) NOT NULL
Go

To listout available User defined data type(s) in SQLServerBuddy Database:
USE SQLServerBuddy
Go

SELECT t.[Name] 'User Defined Type',
TYPE_NAME(t.system_type_id) 'System Type',
t.max_length 'Length'
FROM sys.types t
WHERE T.[schema_id] =1
AND t.is_table_type = 0
Go

Result:

Using User defined data types in a table :
USE SQLServerBuddy
Go

IF OBJECT_ID('Tb_Friend','U') IS NULL
CREATE TABLE Tb_Friend
(
FriendID INT IDENTITY(1,1),
FriendName NAME,
FriendGender GENDER,
FriendAge AGE
)
Go

To listout all User defined data types used in Table(s):

SELECT OBJECT_NAME(ut.[object_id]) 'Object Name',
COL_NAME(ut.[object_id],ut.column_id) 'Column Name',
TYPE_NAME(ut.user_type_id) 'User Defined Type',
TYPE_NAME(t.system_type_id) 'System Type',
t.max_length 'Length'
FROM sys.column_type_usages UT JOIN sys.types t
ON (UT.user_type_id = T.user_type_id)
WHERE T.[schema_id] =1

Result: