获取数据库表字段描述 类型 大小等信息

SELECT   TableName = CASE   
                WHEN a.colorder = 1 THEN d.name  
                ELSE ''  
              END,  
         TableDesc = CASE   
                 WHEN a.colorder = 1 THEN Isnull(f.VALUE,'')  
                 ELSE ''  
               END,  
         ColumnSequence = a.colorder,  
         FieldName = a.name,  
         Flag = CASE   
                WHEN Columnproperty(a.id,a.name,'IsIdentity') = 1 THEN '√'  
                ELSE ''  
              END,  
         --IsPK = CASE 
         --       WHEN EXISTS (SELECT 1  
         --                    FROM   sysobjects  
         --                    WHERE  xtype = 'PK'  
         --                           AND name IN (SELECT name  
         --                                        FROM   sysindexes  
         --                                        WHERE  indid IN (SELECT indid  
         --                                                         FROM   sysindexkeys  
         --                                                         WHERE  id = a.id  
         --                                                                AND colid = a.colid))) THEN '√'  
         --       ELSE ''  
         --     END, /*此处效率偏低，待解决*/
         FieldType = b.name,  
         BitDigit = a.length,  
         Length = Columnproperty(a.id,a.name,'PRECISION'),  
         DecimalDigit = Isnull(Columnproperty(a.id,a.name,'Scale'),0),  
         IsNullable = CASE   
                 WHEN a.isnullable = 1 THEN '1'  
                 ELSE '0'  
               END,  
         DefaultValue = Isnull(e.TEXT,''),  
         ColumnDesc = Isnull(g.[value],'')  
FROM     syscolumns a  
         LEFT JOIN systypes b  
           ON a.xusertype = b.xusertype  
         INNER JOIN sysobjects d  
           ON a.id = d.id  
              AND d.xtype = 'U'  
              AND d.name <> 'dtproperties'  
         LEFT JOIN syscomments e  
           ON a.cdefault = e.id  
         LEFT JOIN sys.extended_properties g  
           ON a.id = g.major_id  
              AND a.colid = g.minor_id  
         LEFT JOIN sys.extended_properties f  
           ON d.id = f.major_id  
              AND f.minor_id = 0 AND f.name='MS_Description' /*注意此处条件限制*/
where d.name = 'Prd_Product'   
ORDER BY a.id, a.colorder