SELECT
表名=CASE WHEN a.colorder=1 THEN d.name ELSE '' END,
表说明=CASE WHEN a.colorder=1 THEN isnull(f.value,'') ELSE '' END,
--字段序号=a.colorder,
字段名=a.name,
字段说明=isnull(g.[value],''),
标识=CASE WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN '√'ELSE '' END,
主键=CASE
WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype='PK' 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,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=CASE WHEN a.isnullable=1 THEN '√'ELSE '' END,
默认值=isnull(e.text,'')
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='orders' --如果只查询指定表,加上此条件
WHERE 1=1
--AND d.name = 'om_sa'
--AND CAST(f.value AS VARCHAR(1000)) LIKE '%账期%'
ORDER BY a.id,a.colorder