1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
| 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], ''),
'' AS '业务含义',
'' AS '业务规则',
'主键' = 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
'YES'
ELSE
'NO'
END,
'是否允许空' = CASE
WHEN a.isnullable = 1 THEN
'YES'
ELSE
'NO'
END,
'字段类型类型' = b.name,
'长度' = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
'精度' = a.length,
'小数位数' = isnull(
COLUMNPROPERTY(a.id, a.name, 'Scale'),
0
),
'默认值' = 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 = 'attract_database_type' --如果只查询指定表,加上此条件
ORDER BY
a.id,
a.colorder;
|
