SQL SERVER 查詢表的欄位名、資料型別和最大長度

查詢表的欄位名、資料型別和最大長度：

 USE test2   --資料庫test2，表fruits；
 GO
 SELECT syscolumns.name AS 欄位名,systypes.name AS 資料型別,syscolumns.length AS 長度 
 FROM syscolumns INNER JOIN systypes ON systypes.xtype=syscolumns.xtype
 WHERE id=(SELECT id FROM sysobjects WHERE  name='fruits')

 SELECT sys.all_columns.name as aa, max_length as bb 
 FROM sys.all_columns
      INNER JOIN sysobjects ON sysobjects.id=sys.all_columns.object_id
 WHERE sysobjects.xtype='u' AND sysobjects.name='fruits'