sqlserver获得表信息

                SELECT
                d.name tablename,
                a.name fieldname,
                b.name fieldtype,
                a.length length,
                COLUMNPROPERTY(a.id,a.name,'PRECISION') precision,
                isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) scale,
                case when a.isnullable=1 then '1'else '0' end nullable,
                isnull(e.text,'') defaultvalue,
                case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end isidentity,
                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 '1' else '0' end  pk
                FROM syscolumns a
                left   join   systypes   b   on   a.xtype=b.xusertype  
                left join syscomments e on a.cdefault=e.id
                inner join sysobjects d on a.id=d.id and  d.xtype='U'  and d.name<>'dtproperties'
                where d.name = 'tableName'