SELECT  t.name AS [table],
        c.name AS [column],
        c.system_type_id,
        typ.name AS [type],
        c.max_length,
        fg.name AS [filegroup]
FROM    sys.columns c
        JOIN sys.types typ ON c.system_type_id = typ.user_type_id
        JOIN sys.tables t ON c.object_id = t.object_id
        JOIN sys.data_spaces ds ON t.lob_data_space_id = ds.data_space_id
        JOIN sys.filegroups fg ON ds.data_space_id = fg.data_space_id
WHERE   fg.name LIKE 'Primary%'
        AND (
             (
              -- nvarchar, varbinary, varchar
              typ.system_type_id IN (231, 165, 167)
              -- (MAX)
              AND c.max_length = -1
             )
             -- text, image, xml, ntext
             OR typ.system_type_id IN (35, 34, 241, 99)
            )
ORDER BY t.name,
        fg.name;