SQL Query to get largest datatype in schema

Question

What is the query to get the top 5 ...data types ...used in a DB by size? blob > int

Answer 1

The sys.types and sys.systypes have some of that info, play around with these queries

select *
from sys.types

select * 
from sys.systypes

Answer 2

What about trying this. It uses the tables sysobjects, syscolumns and systypes to find table name, column name, type and length in bytes. You can select more fields as you need from syscolumns or systypes.

SELECT 
    top 5
    so.name as tablename,
    sc.name as columnname,
    st.name as typename,
    sc.length as columnlength_bytes
FROM 
    syscolumns sc
INNER JOIN
    sysobjects so
    on sc.id = so.id
INNER JOIN
    systypes st
    on sc.xtype = st.xtype
WHERE 
    so.xtype = 'U'
ORDER BY 
    sc.length desc