sql server display all user columns with nvarchar(Max) or normal DateTime

Entity framework (EF) Code First default uses nvarchar(max) for string - thats a performance overhead.


check all columns with nvarchar(max):

select t.name as tablename,c.name as columnname,c.max_length from sys.columns c join sys.tables t on c.object_id=t.object_id Where c.max_length=-1


check all columns with normal datetime (EF better use datetime2):

select t.name as tablename,c.name as columnname,c.max_length from sys.columns c join sys.tables t on c.object_id=t.object_id where system_type_id=61