-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql server 產生 markdown 資料字典.sql
166 lines (163 loc) · 7.21 KB
/
sql server 產生 markdown 資料字典.sql
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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
select 'schema_name' , '|' ,
'table_name' , '|' ,
'column_name' , '|' ,
'data_type' , '|' ,
'data_type_ext' , '|' ,
'nullable' , '|' ,
'default_vale' , '|' ,
'primary_key' , '|' ,
'foreign_key' , '|' ,
'unique_key' , '|' ,
'check_contraint' , '|' ,
'computed_column_definition' , '|' ,
'comments' , '|' UNION ALL
select
'---' , '|' ,
'---' , '|' ,
'---' , '|' ,
'---' , '|' ,
'---' , '|' ,
'---' , '|' ,
'---' , '|' ,
'---' , '|' ,
'---' , '|' ,
'---' , '|' ,
'---' , '|' ,
'---' , '|' ,
'---' , '|' UNION ALL
select schema_name(tab.schema_id) as schema_name, '|' ,
tab.name as table_name, '|' ,
col.name as column_name, '|' ,
t.name as data_type, '|' ,
t.name +
case when t.is_user_defined = 0 then
isnull('(' +
case when t.name in ('binary', 'char', 'nchar',
'varchar', 'nvarchar', 'varbinary') then
case col.max_length
when -1 then 'MAX'
else
case when t.name in ('nchar',
'nvarchar') then
cast(col.max_length/2
as varchar(4))
else cast(col.max_length
as varchar(4))
end
end
when t.name in ('datetime2', 'datetimeoffset',
'time') then
cast(col.scale as varchar(4))
when t.name in ('decimal', 'numeric') then
cast(col.precision as varchar(4)) + ', ' +
cast(col.scale as varchar(4))
end + ')', '')
else ':' +
(select c_t.name +
isnull('(' +
case when c_t.name in ('binary', 'char',
'nchar', 'varchar', 'nvarchar',
'varbinary') then
case c.max_length
when -1 then 'MAX'
else
case when t.name in
('nchar',
'nvarchar') then
cast(c.max_length/2
as varchar(4))
else cast(c.max_length
as varchar(4))
end
end
when c_t.name in ('datetime2',
'datetimeoffset', 'time') then
cast(c.scale as varchar(4))
when c_t.name in ('decimal', 'numeric') then
cast(c.precision as varchar(4)) + ', '
+ cast(c.scale as varchar(4))
end + ')', '')
from sys.columns as c
inner join sys.types as c_t
on c.system_type_id = c_t.user_type_id
where c.object_id = col.object_id
and c.column_id = col.column_id
and c.user_type_id = col.user_type_id
)
end as data_type_ext, '|' ,
case when col.is_nullable = 0 then 'N'
else 'Y' end as nullable, '|' ,
case when def.definition is not null then def.definition
else '' end as default_value, '|' ,
case when pk.column_id is not null then 'PK'
else '' end as primary_key, '|' ,
case when fk.parent_column_id is not null then 'FK'
else '' end as foreign_key, '|' ,
case when uk.column_id is not null then 'UK'
else '' end as unique_key, '|' ,
case when ch.check_const is not null then ch.check_const
else '' end as check_contraint, '|' ,
cc.definition as computed_column_definition, '|' ,
ep.value as comments , '|'
from sys.tables as tab
left join sys.columns as col
on tab.object_id = col.object_id
left join sys.types as t
on col.user_type_id = t.user_type_id
left join sys.default_constraints as def
on def.object_id = col.default_object_id
left join (
select index_columns.object_id,
index_columns.column_id
from sys.index_columns
inner join sys.indexes
on index_columns.object_id = indexes.object_id
and index_columns.index_id = indexes.index_id
where indexes.is_primary_key = 1
) as pk
on col.object_id = pk.object_id
and col.column_id = pk.column_id
left join (
select fc.parent_column_id,
fc.parent_object_id
from sys.foreign_keys as f
inner join sys.foreign_key_columns as fc
on f.object_id = fc.constraint_object_id
group by fc.parent_column_id, fc.parent_object_id
) as fk
on fk.parent_object_id = col.object_id
and fk.parent_column_id = col.column_id
left join (
select c.parent_column_id,
c.parent_object_id,
'Check' check_const
from sys.check_constraints as c
group by c.parent_column_id,
c.parent_object_id
) as ch
on col.column_id = ch.parent_column_id
and col.object_id = ch.parent_object_id
left join (
select index_columns.object_id,
index_columns.column_id
from sys.index_columns
inner join sys.indexes
on indexes.index_id = index_columns.index_id
and indexes.object_id = index_columns.object_id
where indexes.is_unique_constraint = 1
group by index_columns.object_id,
index_columns.column_id
) as uk
on col.column_id = uk.column_id
and col.object_id = uk.object_id
left join sys.extended_properties as ep
on tab.object_id = ep.major_id
and col.column_id = ep.minor_id
and ep.name = 'MS_Description'
and ep.class_desc = 'OBJECT_OR_COLUMN'
left join sys.computed_columns as cc
on tab.object_id = cc.object_id
and col.column_id = cc.column_id
--order by schema_name,
-- table_name,
-- column_name;