SELECT c.name '字段名', TYPE_NAME(c.system_type_id)+'('+CONVERT(VARCHAR(20),c.max_length)+')''字段类型',CASE c.is_nullable WHEN0THEN'否'ELSE'是'END'是否允许为空',convert(varchar(100), isnull(cmts.text,''))'默认值',p.value'备注'FROM
sys.objects obj
JOIN sys.columns c ON c.object_id = obj.object_id
LEFTJOIN sys.extended_properties p ON c.object_id = p.major_id AND p.minor_id=c.column_id
LEFTJOIN sys.syscomments cmts ON c.default_object_id = cmts.id
WHERE
obj.name='CustomerProductPool'
格式二
SELECTDISTINCT
d.name,
f.valueFROM
syscolumns a
LEFTJOIN systypes b ON a.xusertype= b.xusertype
INNERJOIN sysobjects d ON a.id= d.id
AND d.xtype='U'AND d.name<>'dtproperties'LEFTJOIN syscomments e ON a.cdefault= e.id
LEFTJOIN sys.extended_properties g ON a.id= G.major_id
AND a.colid= g.minor_id
LEFTJOIN sys.extended_properties f ON d.id= f.major_id
AND f.minor_id=0SELECT
a.colorder as colOrder,
d.name as tableName,
a.name as sqlName,convert(int,casewhen COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1then1else0end)as isIndex,convert(int,casewhenexists(SELECT1FROM 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)))then1else0end)as isPk,
b.name as sqlTypeName,
a.length as'length',convert(int,COLUMNPROPERTY(a.id,a.name,'PRECISION'))as'size',convert(int,isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0))as'scale',convert(int,casewhen a.isnullable=1then1else0end)as'nullable',convert(varchar(100), isnull(e.text,''))as defaultVal,convert(varchar(255), isnull(g.[value],''))as remarks
FROM
syscolumns a
leftjoin
systypes b
on
a.xusertype=b.xusertype
innerjoin
sysobjects d
on
a.id=d.id and d.xtype='U'and d.name<>'dtproperties'leftjoin
syscomments e
on
a.cdefault=e.id
leftjoin
sys.extended_properties g
on
a.id=G.major_id and a.colid=g.minor_id
leftjoin
sys.extended_properties f
on
d.id=f.major_id and f.minor_id=0where1=1-- d.name=? orderby
a.id,a.colorder