sql server sql查询数据库的表，字段，主键，自增，字段类型等信息

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)) descr
FROM SYSCOLUMNS t1
left 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.indid
left join systypes  t5 on  t1.xtype=t5.xtype
left join sys.extended_properties t6   on  t1.id=t6.major_id   and   t1.colid=t6.minor_id
left join SYSOBJECTS tb  on  tb.id=t1.id
where 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 t
FROM syscolumns
WHERE (id = OBJECT_ID('view_UserInfo'))