Is there a way to see every data type that has been used in a database? if so, how do I do it?
Something like this:
SELECT
DISTINCT t.name
FROM
sys.objects o
INNER JOIN sys.columns c ON
c.object_id = o.object_id
INNER JOIN sys.types t ON
t.system_type_id = c.system_type_id
WHERE
o.type IN ('U')
The sys.columns table lists all the columns in your tables. The query is trying to pick up only columns from user-defined tables from those. You may need to expand it/modify it depending on your requirements.