获取数据库表字段

SELECT

表名=casewhena.colorder=1thend.nameelse''end,

表说明=casewhena.colorder=1thenisnull(f.value,'')else''end,

字段序号=a.colorder,

字段名=a.name,

标识=casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''end,

主键=casewhenexists(SELECT1FROMsysobjectswherextype='PK'andnamein(

SELECTnameFROMsysindexesWHEREindidin(

SELECTindidFROMsysindexkeysWHEREid=a.idANDcolid=a.colid

)))then'√'else''end,

类型=b.name,

占用字节数=a.length,

长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),

小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),

允许空=casewhena.isnullable=1then'√'else''end,

默认值=isnull(e.text,''),

字段说明=isnull(g.[value],'')

FROMsyscolumnsa

leftjoinsystypesbona.xusertype=b.xusertype

innerjoinsysobjectsdona.id=d.idandd.xtype='U'andd.name<>'dtproperties'

leftjoinsyscommentSEOna.cdefault=e.id

leftjoinsys.extended_propertiesgona.id=g.major_idanda.colid=g.minor_id

leftjoinsys.extended_propertiesfond.id=f.major_idandf.minor_id=0

whered.name='CJ_Products_bak'--如果只查询指定表,加上此条件

orderbya.id,a.colorder