forked from ktaranov/sqlserver-kit
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Auto_Generate_Database_Documentation.sql
185 lines (179 loc) · 9.89 KB
/
Auto_Generate_Database_Documentation.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
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
/*
.EXAMPLE
Connect SSMS to the database in which you wish to create extended properties and hit F5.
.DESCRIPTION:
This script will not create the extended properties, but auto generate the commands to do so.
The actual value (@value parameter) of the extended property still has to be manually input.
This script will automatically add the user and date to the extended property value.
.NOTE
Version: 1.1
Modified: 2018-03-30 23:40:00 UTC+3 Konstantin Taranov
Link: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Auto_Generate_Database_Documentation.sql
Created: 2015-11-25
Author: (C) Andy Jones mailto:[email protected]
*/
DECLARE @Parameter TABLE
(
[type_desc] sysname NOT NULL,
parameter sysname NOT NULL,
val NVARCHAR(100) NOT NULL,
PRIMARY KEY ( [type_desc], parameter )
);
/*set up the data for each object type specifying the correct value for each parameter.*/
INSERT INTO @Parameter
( [type_desc], parameter, val )
VALUES ( N'CHECK_CONSTRAINT', N'value', N'_replace_value' ),
( N'CHECK_CONSTRAINT', N'level0type', N'SCHEMA' ),
( N'CHECK_CONSTRAINT', N'level0name', N'_replace_schemaname' ),
( N'CHECK_CONSTRAINT', N'level1type', N'TABLE' ),
( N'CHECK_CONSTRAINT', N'level1name', N'_replace_parentname' ),
( N'CHECK_CONSTRAINT', N'level2type', N'CONSTRAINT' ),
( N'CHECK_CONSTRAINT', N'level2name', N'_replace_name' ),
( N'FOREIGN_KEY_CONSTRAINT', N'value', N'_replace_value' ),
( N'FOREIGN_KEY_CONSTRAINT', N'level0type', N'SCHEMA' ),
( N'FOREIGN_KEY_CONSTRAINT', N'level0name', N'_replace_schemaname' ),
( N'FOREIGN_KEY_CONSTRAINT', N'level1type', N'TABLE' ),
( N'FOREIGN_KEY_CONSTRAINT', N'level1name', N'_replace_parentname' ),
( N'FOREIGN_KEY_CONSTRAINT', N'level2type', N'CONSTRAINT' ),
( N'FOREIGN_KEY_CONSTRAINT', N'level2name', N'_replace_name' ),
( N'PRIMARY_KEY_CONSTRAINT', N'value', N'_replace_value' ),
( N'PRIMARY_KEY_CONSTRAINT', N'level0type', N'SCHEMA' ),
( N'PRIMARY_KEY_CONSTRAINT', N'level0name', N'_replace_schemaname' ),
( N'PRIMARY_KEY_CONSTRAINT', N'level1type', N'TABLE' ),
( N'PRIMARY_KEY_CONSTRAINT', N'level1name', N'_replace_parentname' ),
( N'PRIMARY_KEY_CONSTRAINT', N'level2type', N'CONSTRAINT' ),
( N'PRIMARY_KEY_CONSTRAINT', N'level2name', N'_replace_name' ),
( N'UNIQUE_CONSTRAINT', N'value', N'_replace_value' ),
( N'UNIQUE_CONSTRAINT', N'level0type', N'SCHEMA' ),
( N'UNIQUE_CONSTRAINT', N'level0name', N'_replace_schemaname' ),
( N'UNIQUE_CONSTRAINT', N'level1type', N'TABLE' ),
( N'UNIQUE_CONSTRAINT', N'level1name', N'_replace_parentname' ),
( N'UNIQUE_CONSTRAINT', N'level2type', N'CONSTRAINT' ),
( N'UNIQUE_CONSTRAINT', N'level2name', N'_replace_name' ),
( N'SQL_STORED_PROCEDURE', N'value', N'_replace_value' ),
( N'SQL_STORED_PROCEDURE', N'level0type', N'SCHEMA' ),
( N'SQL_STORED_PROCEDURE', N'level0name', N'_replace_schemaname' ),
( N'SQL_STORED_PROCEDURE', N'level1type', N'PROCEDURE' ),
( N'SQL_STORED_PROCEDURE', N'level1name', N'_replace_name' ),
( N'SQL_STORED_PROCEDURE', N'level2type', N'NULL' ),
( N'SQL_STORED_PROCEDURE', N'level2name', N'NULL' ),
( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'value', N'_replace_value' ),
( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'level0type', N'SCHEMA' ),
( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'level0name',
N'_replace_schemaname' ),
( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'level1type', N'FUNCTION' ),
( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'level1name', N'_replace_name' ),
( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'level2type', N'NULL' ),
( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'level2name', N'NULL' ),
( N'SQL_SCALAR_FUNCTION', N'value', N'_replace_value' ),
( N'SQL_SCALAR_FUNCTION', N'level0type', N'SCHEMA' ),
( N'SQL_SCALAR_FUNCTION', N'level0name', N'_replace_schemaname' ),
( N'SQL_SCALAR_FUNCTION', N'level1type', N'FUNCTION' ),
( N'SQL_SCALAR_FUNCTION', N'level1name', N'_replace_name' ),
( N'SQL_SCALAR_FUNCTION', N'level2type', N'NULL' ),
( N'SQL_SCALAR_FUNCTION', N'level2name', N'NULL' ),
( N'USER_TABLE', N'value', N'_replace_value' ),
( N'USER_TABLE', N'level0type', N'SCHEMA' ),
( N'USER_TABLE', N'level0name', N'_replace_schemaname' ),
( N'USER_TABLE', N'level1type', N'TABLE' ),
( N'USER_TABLE', N'level1name', N'_replace_name' ),
( N'USER_TABLE', N'level2type', N'NULL' ),
( N'USER_TABLE', N'level2name', N'NULL' ),
( N'INDEX', N'value', N'_replace_value' ),
( N'INDEX', N'level0type', N'SCHEMA' ),
( N'INDEX', N'level0name', N'_replace_schemaname' ),
( N'INDEX', N'level1type', N'TABLE' ),
( N'INDEX', N'level1name', N'_replace_parentname' ),
( N'INDEX', N'level2type', N'INDEX' ),
( N'INDEX', N'level2name', N'_replace_name' ),
( N'COLUMN', N'value', N'_replace_value' ),
( N'COLUMN', N'level0type', N'SCHEMA' ),
( N'COLUMN', N'level0name', N'_replace_schemaname' ),
( N'COLUMN', N'level1type', N'TABLE' ),
( N'COLUMN', N'level1name', N'_replace_parentname' ),
( N'COLUMN', N'level2type', N'COLUMN' ),
( N'COLUMN', N'level2name', N'_replace_name' );
WITH Obj /*union all objects on which to create extended properties. Objects, columns and indexes.*/
AS ( SELECT parentname = COALESCE(OBJECT_NAME(obj.parent_object_id),
obj.name) ,
name = obj.name ,
schemaname = SCHEMA_NAME(obj.[schema_id]) ,
[type_desc] = obj.[type_desc] ,
major_id = obj.[object_id] ,
minor_id = 0,
class_desc = N'OBJECT_OR_COLUMN'
FROM sys.objects AS obj
WHERE obj.is_ms_shipped = 0
UNION ALL
SELECT parentname = OBJECT_NAME(c.[object_id]) ,
name = c.name ,
schemaname = OBJECT_SCHEMA_NAME(c.[object_id]) ,
[type_desc] = N'COLUMN' ,
major_id = c.[object_id] ,
minor_id = c.column_id,
class_desc = N'OBJECT_OR_COLUMN'
FROM sys.columns AS c
WHERE OBJECTPROPERTYEX(c.[object_id], 'IsMSShipped') = 0
AND OBJECTPROPERTYEX(c.[object_id], 'IsUserTable') = 1 --only document table columns, not views/functions. Remove predicate if required.
UNION ALL
SELECT parentname = OBJECT_NAME(i.[object_id]) ,
name = i.name ,
schemaname = OBJECT_SCHEMA_NAME(i.[object_id]) ,
[type_desc] = N'INDEX' ,
major_id = i.[object_id] ,
minor_id = i.index_id,
class_desc = N'INDEX'
FROM sys.indexes AS i
WHERE OBJECTPROPERTYEX(i.[object_id], 'IsMSShipped') = 0
AND i.is_primary_key = 0 --the constraint is already documented, don't document the index too. Remove predicate if required.
AND i.is_unique_constraint = 0 --the constraint is already documented, don't document the index too. Remove predicate if required.
AND i.[type_desc] <> N'HEAP' --the table is already documented, don't document the heap index row too.
),
/*Join objects on which to create extended properties to the parameters, performing string replacement where necessary.*/
Parameter_Value
AS ( SELECT o.major_id ,
o.minor_id ,
o.class_desc,
p.parameter ,
[name] = N'MS_Description',
val = CASE p.val
WHEN N'_replace_schemaname' THEN o.schemaname
WHEN N'_replace_parentname' THEN o.parentname
WHEN N'_replace_name' THEN o.name
WHEN N'_replace_value'
THEN SYSTEM_USER + N' '
+ CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103)
+ N': ' + p.val
ELSE p.val
END
FROM @Parameter AS p
INNER JOIN Obj AS o ON o.[type_desc] = p.[type_desc] COLLATE DATABASE_DEFAULT
)
/*pivot the result set so we have one correctly formatted extended property create statement per object.*/
SELECT Add_Extended_Property = N'EXECUTE sp_addextendedproperty'
+ N' @name = ''' + [name] + N''', @value = ' + [value]
+ N', @level0type = ' + [level0type] + N', @level0name = '
+ [level0name] + N', @level1type = ' + [level1type]
+ N', @level1name = ' + [level1name] + N', @level2type = '
+ [level2type] + N', @level2name = ' + [level2name] + N';'
FROM ( SELECT pv.major_id ,
pv.minor_id ,
pv.class_desc,
pv.[name],
pv.parameter ,
val = CASE pv.val
WHEN N'NULL' THEN pv.val
ELSE '''' + pv.val + ''''
END
FROM Parameter_Value AS pv
WHERE NOT EXISTS ( SELECT *
FROM sys.extended_properties AS ep
WHERE ep.major_id = pv.major_id
AND ep.minor_id = pv.minor_id
AND ep.class_desc = pv.class_desc
AND ep.[name] = pv.[name] )
) AS SourceTable PIVOT
( MIN(val) FOR parameter IN ( [value], [level0type], [level0name],
[level1type], [level1name], [level2type],
[level2name] ) ) AS PivotTable
ORDER BY Add_Extended_Property;