T-sql get alll columns name and column type from table

select a.name,case a.precision when 0  then   case a.is_ansi_padded 
 when 1 then  convert(nvarchar(15),b.name+ '('+convert(nvarchar(10),a.max_length)+')') 
 when 0 then  b.name end else   case a.scale 
 when 0 then b.name  else b.name+ '('+convert(nvarchar(10),a.precision)+','+convert(nvarchar(10),a.scale)+')'
 end
end 
 as typelength from sys.columns a left join sys.types b on a.system_type_id=b.system_type_id and a.user_type_id=b.user_type_id where a.object_id =(select object_id from sys.objects where name ='Address')

　　

转载于:https://www.cnblogs.com/gzh4455/archive/2013/06/13/3133505.html