-
Notifications
You must be signed in to change notification settings - Fork 3
/
generate-table-xml.sql
236 lines (223 loc) · 6.98 KB
/
generate-table-xml.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
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
/*
generate-table-xml.sql will document the Tables, Columns and Foreign keys from a database as an Xmldocument.
*/
select
tableXml
from
(
select
'<Database name="'+ db.name COLLATE DATABASE_DEFAULT
+ '" collation="' + db.collation_name COLLATE DATABASE_DEFAULT
+ '" compatibilityLevel="' + cast(db.compatibility_level as varchar)
+ '" createDate="' + cast(db.create_date as varchar)
+ '">' as [tableXml],
'0.1' as [sort_order]
from
sys.databases db
where
database_id = DB_ID()
UNION ALL
select
' <File name="' + mf.name COLLATE DATABASE_DEFAULT
+ '" type="' + mf.type_desc COLLATE DATABASE_DEFAULT
+ '" physical="' + mf.physical_name COLLATE DATABASE_DEFAULT
+ '"/>' as [tableXml],
'0.1.0' + cast(mf.file_id as varchar) as [sort_order]
from
sys.master_files mf
where
database_id = DB_ID()
UNION ALL
select
' <Objects>' as [tableXml],
'0.2' as [sort_order]
UNION ALL
select
' <Table tableId="' + cast(object_id as varchar)
+ '" schema="' + s.name COLLATE DATABASE_DEFAULT
+ '" name="' + t.name COLLATE DATABASE_DEFAULT
+ '">' as [tableXml],
cast(object_id as varchar) + '.0' as [sort_order]
from
sys.tables t
inner join
sys.schemas s on s.schema_id = t.schema_id
UNION ALL
select
' <Rows count="' + cast(p.rows as varchar) + '"/>' as [xml],
cast(t.object_id as varchar) + '.00' as [sort_order]
from
sys.tables t
inner join
sys.indexes i ON t.object_id = i.object_id
inner join
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
where
t.name not like 'dt%'
and i.object_id > 255
and i.index_id <= 1
group by
t.object_id, p.rows
UNION ALL
select
' <Column columnId="' + cast(c.column_id as varchar)
+ '" name="' + c.name COLLATE DATABASE_DEFAULT
+ '" type="' + ty.name COLLATE DATABASE_DEFAULT
+ '" maxLength="' + cast(c.max_length as varchar)
+ '" precision="' + cast(c.precision as varchar)
+ '" scale="' + cast(c.scale as varchar)
+ '"/>' as [xml],
cast(t.object_id as varchar) + '.' + right('00000' + cast(c.column_id as varchar), 3) as [sort_order]
--*
from sys.tables t
inner join
sys.columns c
on t.object_id = c.object_id
inner join
sys.types ty
on c.user_type_id = ty.user_type_id
where t.type = 'U'
UNION ALL
select
' </Table>' as [tableXml],
cast(object_id as varchar) + '.999' as [sort_order]
from
sys.tables t
UNION ALL
select
' </Objects>' as [tableXml],
'99999999999999.999' as [sort_order]
UNION ALL
select
'</Database>' as [tableXml],
'99999999999999.9999' as [sort_order]
UNION ALL
select
' <ForeignKey name="' + fk.name COLLATE DATABASE_DEFAULT
--+ '" tableId="' + cast(fkc.parent_object_id as varchar)
+ '" columnId="' + cast(fkc.parent_column_id as varchar)
+ '" refTableId="' + CAST(fkc.referenced_object_id as varchar)
+ '" refColumnId="' + CAST(fkc.referenced_column_id as varchar)
+ '"/>' as [tableXml],
cast(fkc.parent_object_id as varchar) + '.000.' + right('000' + cast(fkc.parent_column_id as varchar), 3) as [sort_order]
from
sys.foreign_key_columns fkc
inner join
sys.foreign_keys fk
on
fk.object_id = fkc.constraint_object_id
UNION ALL
select
' <View viewId="' + cast(object_id as varchar)
+ '" schema="' + s.name COLLATE DATABASE_DEFAULT
+ '" name="' + v.name COLLATE DATABASE_DEFAULT
+ '">' as [tableXml],
cast(object_id as varchar) + '.0' as [sort_order]
from
sys.views v
inner join
sys.schemas s on s.schema_id = v.schema_id
UNION ALL
select
' <Column columnId="' + cast(c.column_id as varchar)
+ '" name="' + c.name COLLATE DATABASE_DEFAULT
+ '" type="' + ty.name COLLATE DATABASE_DEFAULT
+ '" maxLength="' + cast(c.max_length as varchar)
+ '" precision="' + cast(c.precision as varchar)
+ '" scale="' + cast(c.scale as varchar)
+ '"/>' as [xml],
cast(v.object_id as varchar) + '.' + right('00000' + cast(c.column_id as varchar), 3) as [sort_order]
--*
from sys.views v
inner join
sys.columns c
on v.object_id = c.object_id
inner join
sys.types ty
on c.user_type_id = ty.user_type_id
--where v.type = 'U'
UNION ALL
select
' </View>' as [tableXml],
cast(object_id as varchar) + '.999' as [sort_order]
from
sys.views v
-- STORED PROCEDURES
UNION ALL
select
' <Procedure procedureId="' + cast(object_id as varchar)
+ '" schema="' + s.name COLLATE DATABASE_DEFAULT
+ '" name="' + p.name COLLATE DATABASE_DEFAULT
+ '">' as [tableXml],
cast(object_id as varchar) + '.0' as [sort_order]
from
sys.procedures p
inner join
sys.schemas s on s.schema_id = p.schema_id
UNION ALL
select
' </Procedure>' as [tableXml],
cast(object_id as varchar) + '.999' as [sort_order]
from
sys.procedures p
-- Functions
UNION ALL
select
' <Function functionId="' + cast(object_id as varchar)
+ '" schema="' + s.name COLLATE DATABASE_DEFAULT
+ '" name="' + o.name COLLATE DATABASE_DEFAULT
+ '">' as [tableXml],
cast(object_id as varchar) + '.0' as [sort_order]
from
sys.objects o
inner join
sys.schemas s on s.schema_id = o.schema_id
WHERE
o.type IN (N'FN', N'IF', N'TF', N'FS', N'FT')
-- From http://msdn.microsoft.com/en-us/library/ms177596.aspx:
-- FN SQL_SCALAR_FUNCTION
-- FS Assembly (CLR) scalar-function
-- FT Assembly (CLR) table-valued function
-- IF SQL_INLINE_TABLE_VALUED_FUNCTION
-- TF SQL_TABLE_VALUED_FUNCTION
UNION ALL
select
' </Function>' as [tableXml],
cast(object_id as varchar) + '.999' as [sort_order]
from
sys.objects o
WHERE
o.type IN (N'FN', N'IF', N'TF', N'FS', N'FT')
-- Referencing objects
UNION ALL
select
' <Reference name="' + OBJECT_NAME(s.referencing_id) COLLATE DATABASE_DEFAULT
+ '" refName="' + ISNULL(OBJECT_NAME(s.referenced_id), s.referenced_entity_name) COLLATE DATABASE_DEFAULT
+ '" objectId="' + cast(s.referencing_id as varchar)
+ CASE WHEN s.referencing_minor_id = 0 THEN '' ELSE '" columnId="' + cast(s.referencing_minor_id as varchar) END
+ ISNULL('" refObjectId="' + ISNULL(cast(s.referenced_id as varchar), OBJECT_ID(s.referenced_entity_name)), '')
+ CASE WHEN s.referenced_minor_id = 0 THEN '' ELSE '" refColumnId="' + cast(s.referenced_minor_id as varchar) END
+ '"/>' as [tableXml],
cast(s.referencing_id as varchar) + '.0000.' + right('000' + cast(s.referenced_minor_id as varchar), 3) as [sort_order]
from
sys.sql_expression_dependencies s
where
s.referencing_id in
(
select o.object_id--, o.type, o.type_desc
from sys.objects o
where o.type not in (
'S' -- SYSTEM_TABLE
,'IT' -- INTERNAL_TABLE
,'TT' -- TYPE_TABLE
,'D' -- DEFAULT_CONSTRAINT
,'PK' -- PRIMARY_KEY_CONSTRAINT
,'UQ' -- UNIQUE_CONSTRAINT
,'F' -- FOREIGN_KEY_CONSTRAINT
,'C' -- CHECK_CONSTRAINT
,'TR' -- SQL_TRIGGER
,'SQ' -- SERVICE_QUEUE
)
)
) qry
order by qry.sort_order