select AA.columnname,AA.tablename ,

AA.typename,
AA.max_length,
AA.precision,
AA.scale ,BB.remark from (
select a.name as columnname,b.name tablename ,

c.name typename,
a.max_length,
a.precision,
a.scale
from sys.columns a,
sys.objects b,
sys.types c
where a.object_id=b.object_id 
and a.user_type_id=c.user_type_id
and b.type=‘u‘
 ) AA LEFT JOIN


(select a.name as columnname,b.name tablename ,
c.name typename,
a.max_length,
a.precision,
a.scale,cast(isnull(e.[value],‘‘) as nvarchar(100)) as remark
from sys.columns a,
sys.objects b,
sys.types c,
sys.extended_properties e
where a.object_id=b.object_id 
and a.user_type_id=c.user_type_id
and b.type=‘u‘
and  e.major_id=b.object_id 
and e.minor_id=a.column_id and e.class=1
) BB  on AA.columnname=BB.columnname AND AA.tablename=BB.tablename ORDER BY AA.tablename

select a.name, cast(isnull(e.[value],‘‘) as nvarchar(100)) as remark 
from  sys.columns a inner join sys.objects c on a.object_id=c.object_id and c.type=‘u‘
left join sys.extended_properties e on e.major_id=c.object_id 
and e.minor_id=a.column_id and e.class=1 where c.name=‘AssistEngineer‘

select a.name as columnname,b.name tablename ,
c.name typename,
a.max_length,
a.precision,
a.scale,cast(isnull(e.[value],‘‘) as nvarchar(100)) as remark
from sys.columns a,
sys.objects b,
sys.types c,
sys.extended_properties e
where a.object_id=b.object_id 
and a.user_type_id=c.user_type_id
and b.type=‘u‘
and  e.major_id=b.object_id 
and e.minor_id=a.column_id and e.class=1
--and c.name in(‘varchar‘,‘nvarchar‘,‘char‘,‘nchar‘,‘text‘,‘ntext‘)
--and object_name(a.object_id)<>‘t‘


order by b.name