执行SQL后返回的列分别是：表名、列名、列类型、列长度、列描述、是否主键

/**/ /*

谁可以帮我写个SQL执行后返回的列分别是：表名、列名、列类型、列长度、列描述、是否主键
写完整来。就是我一拿过来就可以运行的那种！~！~谢谢哦。


*/


SELECT  
 ( case   when  a.colorder = 1   then  d.name  else   ''   end ) N ' 表名 ' ,
 a.colorder N ' 字段序号 ' ,
 a.name N ' 字段名 ' ,
 ( case   when   COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1   then   ' √ ' else   ''   end ) N ' 标识 ' ,
 ( case   when  ( SELECT   count ( * )
  FROM  sysobjects
  WHERE  (name  in
           ( SELECT  name
           FROM  sysindexes
           WHERE  (id  =  a.id)  AND  (indid  in
                    ( SELECT  indid
                    FROM  sysindexkeys
                    WHERE  (id  =  a.id)  AND  (colid  in
                             ( SELECT  colid
                             FROM  syscolumns
                             WHERE  (id  =  a.id)  AND  (name  =  a.name)))))))  AND
        (xtype  =   ' PK ' )) > 0   then   ' √ '   else   ''   end ) N ' 主键 ' ,
 b.name N ' 类型 ' ,
 a.length N ' 占用字节数 ' ,
  COLUMNPROPERTY (a.id,a.name, ' PRECISION ' )  as  N ' 长度 ' ,
  isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 )  as  N ' 小数位数 ' ,
 ( case   when  a.isnullable = 1   then   ' √ ' else   ''   end ) N ' 允许空 ' ,
  isnull (e. text , '' ) N ' 默认值 ' ,
  isnull (g. [ value ] , '' )  AS  N ' 字段说明 '
-- into ##tx

FROM   syscolumns  a  left   join  systypes b 
on   a.xtype = 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  sysproperties g
on  a.id = g.id  AND  a.colid  =  g.smallid  
order   by   object_name (a.id),a.colorder



-- 方法二


SET  ANSI_NULLS  OFF  

GO

SET  NOCOUNT  ON

GO

SET  LANGUAGE  ' Simplified Chinese '

go

DECLARE   @tbl   nvarchar ( 200 ), @fld   nvarchar ( 200 ), @sql   nvarchar ( 4000 ), @maxlen   int , @sample   nvarchar ( 40 )


SELECT  d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL  INTO  #t

FROM   syscolumns  a,  systypes b,sysobjects d  

WHERE   a.xtype = b.xusertype   and   a.id = d.id   and   d.xtype = ' U '  

DECLARE  read_cursor  CURSOR

FOR   SELECT  TableName,FieldName  FROM  #t


SELECT   TOP   1   ' _TableName                      '  TableName,

             ' FieldName                       '  FieldName, ' TypeName              '  TypeName,

             ' Length '  Length, ' IS_NULL '  IS_NULL, 

             ' MaxLenUsed '   AS  MaxLenUsed, ' Sample Value           '  Sample,

              ' Comment    '  Comment  INTO  #tc  FROM  #t

OPEN  read_cursor

FETCH   NEXT   FROM  read_cursor  INTO   @tbl , @fld

WHILE  ( @@fetch_status   <>   - 1 )   -- - failes

BEGIN

        IF  ( @@fetch_status   <>   - 2 )  --  Missing

        BEGIN

               SET   @sql = N ' SET @maxlen=(SELECT max(len(cast( ' + @fld + '  as nvarchar))) FROM  ' + @tbl + ' ) '

               -- PRINT @sql

               EXEC  SP_EXECUTESQL  @sql ,N ' @maxlen int OUTPUT ' , @maxlen  OUTPUT

               -- print @maxlen

               SET   @sql = N ' SET @sample=(SELECT TOP 1 cast( ' + @fld + '  as nvarchar) FROM  ' + @tbl + '  WHERE len(cast( ' + @fld + '  as nvarchar))= ' + convert ( nvarchar ( 5 ), @maxlen ) + ' ) '

               EXEC  SP_EXECUTESQL  @sql ,N ' @sample varchar(30) OUTPUT ' , @sample  OUTPUT

               -- for quickly   

               -- SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+

                      -- @tbl+' order by 1 desc ))'  

               PRINT   @sql

               print   @sample

               print   @tbl

               EXEC  SP_EXECUTESQL  @sql ,N ' @sample nvarchar(30) OUTPUT ' , @sample  OUTPUT

               INSERT   INTO  #tc  SELECT   * , ltrim ( ISNULL ( @maxlen , 0 ))  as  MaxLenUsed,

                      convert ( nchar ( 20 ), ltrim ( ISNULL ( @sample , '   ' )))  as  Sample, '   '  Comment  FROM  #t  where  TableName = @tbl   and  FieldName = @fld

        END

        FETCH   NEXT   FROM  read_cursor  INTO   @tbl , @fld

END

CLOSE  read_cursor

DEALLOCATE  read_cursor

GO

SET  ANSI_NULLS  ON

GO

SET  NOCOUNT  OFF

GO

select   count ( * )   from  #t

DROP   TABLE  #t

GO

select   count ( * ) - 1    from   #tc
select   *   into  ##tx  from  #tc  order   by  tablename
select   *   from   ##tx