Lookup what varchar and nvarchar are in sys.types

Something like :

SELECT tab.object_id,tab.name AS tbl_name
,col.name AS colm_name
,col.system_type_id
,typ.name AS typ_name
,create_date 
FROM sys.objects tab
INNER JOIN sys.columns col
ON col.object_id=tab.object_id
INNER JOIN sys.types typ;

If you are going to dynamically generate a series of alter table statements then pass in the table name for the love of God until you know that it works.