1、查询数据表的属性(名称、说明、是否主键、数据类型、是否自增)

SELECT t1.name columnName,case when  t4.id is null then 'false' else 'true' end as pkColumn, 	case when  COLUMNPROPERTY( t1.id,t1.name,'IsIdentity') = 1 then 'true' else 'false' end as  autoAdd	,t5.name jdbcType 	,cast(isnull(t6.value,'') as varchar(2000)) descrFROM SYSCOLUMNS t1left join SYSOBJECTS t2 on  t2.parent_obj = t1.id  AND t2.xtype = 'PK' left join SYSINDEXES t3 on  t3.id = t1.id  and t2.name = t3.name  left join SYSINDEXKEYS t4 on t1.colid = t4.colid and t4.id = t1.id and t4.indid = t3.indidleft join systypes  t5 on  t1.xtype=t5.xtypeleft join sys.extended_properties t6   on  t1.id=t6.major_id   and   t1.colid=t6.minor_idleft join SYSOBJECTS tb  on  tb.id=t1.idwhere tb.name='APIInfo' and t5.name<>'sysname' order by t1.colid asc

　　

若只是简单查询列名及字段类型/长度等信息，直接用下面语句即可

SELECT name,type_name(xtype) AS type,length,(type_name(xtype)+'('+CONVERT(varchar,length)+')') as tFROM syscolumnsWHERE (id = OBJECT_ID('view_UserInfo'))