sql server读取表结构

SELECT  
	a.colorder, --序号
	d.name as tableName,--表名
	a.name as fieldName, --字段名
	(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '是' else '否' end) as isIndex, --是否自增列
	(case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id 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) as isPk, --是否主键
	(case when b.name in('varchar','nvarchar','nchar','char') then (b.name +'('+(case when a.length=-1 then 'MAX' else CONVERT(varchar(50), a.length) end)+')') when b.name='decimal' then (b.name+'('+(CONVERT(varchar(50),COLUMNPROPERTY(a.id,a.name,'PRECISION'))+','+CONVERT(varchar(50),isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)))+')') else b.name end) as typeName,--数据类型
	(case when a.isnullable=1 then '是' else '否' end) as 'nullable', --是否允许为空
	convert(varchar(100), isnull(e.text,'')) as defaultVal, --默认值
	convert(varchar(255), isnull(g.[value],'')) as remarks --说明
 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 
 where  d.name='表名'
order by  
		a.id,a.colorder