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 ,
标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'
ELSE ''
END ,
主键 = CASE WHEN EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = so.name
AND so.xtype = 'PK'
WHERE sc.id = a.id
AND sc.colid = a.colid ) THEN '√'
ELSE ''
END ,
外键 = CASE WHEN tony.fkey IS NOT NULL
AND tony.fkey = a.colid THEN '√'
ELSE ''
END ,
外键表 = CASE WHEN tony.fkey IS NOT NULL
AND tony.fkey = a.colid THEN OBJECT_NAME(tony.fkeyid)
ELSE ''
END ,
外键字段 = CASE WHEN tony.fkey IS NOT NULL
AND tony.fkey = a.colid
THEN ( SELECT name
FROM syscolumns
WHERE colid = tony.fkey
AND id = tony.fkeyid
)
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, '') ,
字段说明 = ISNULL(g.[value], '') ,
创建时间 = d.crdate ,
更改时间 = CASE WHEN a.colorder = 1 THEN d.refdate
ELSE NULL
END
FROM dbo.syscolumns a
LEFT JOIN dbo.systypes b ON a.xtype = b.xusertype
INNER JOIN dbo.sysobjects d ON a.id = d.id
AND d.xtype = 'U'
AND d.status >= 0
LEFT JOIN dbo.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
AND g.class_desc = 'OBJECT_OR_COLUMN'
LEFT JOIN sys.extended_properties f ON d.id = f.major_id
AND f.minor_id = 0
LEFT JOIN sysobjects htl ON htl.parent_obj = d.id
AND htl.xtype = 'F'
LEFT JOIN sysforeignkeys tony ON htl.id = tony.constid
WHERE d.name = 'student' --这里输入包含表名称的条件
ORDER BY d.id ,
a.colorder