Skip to content

Microsoft SQL Server

This page gives a sample metadata for Microsoft SQL Server.

Sample

  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
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
{
    "createTime": "2022-11-16 21:05:32",
    "createdBy": "sqlflow-ingester v1.1.7",
    "physicalInstance": "115.159.xx.xx",
    "servers": [
        {
            "databases": [
                {
                    "name": "HR",
                    "schemas": [
                        {
                            "name": "[dbo]",
                            "synonyms": [
                                {
                                    "database": "[HR]",
                                    "name": "OFFICES",
                                    "schema": "[dbo]",
                                    "sourceDbLinkName": "HR",
                                    "sourceName": "LOCATIONS",
                                    "sourceSchema": "DBO"
                                }
                            ],
                            "tables": [
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "char(2)",
                                            "name": "COUNTRY_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(40)",
                                            "name": "COUNTRY_NAME"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "REGION_ID"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "COUNTRIES",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                },
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "DEPARTMENT_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(30)",
                                            "name": "DEPARTMENT_NAME"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "MANAGER_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "LOCATION_ID"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "DEPARTMENTS",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                },
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "EMPLOYEE_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(20)",
                                            "name": "FIRST_NAME"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "LAST_NAME"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "EMAIL"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(20)",
                                            "name": "PHONE_INT"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "date",
                                            "name": "HIRE_DATE"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(10)",
                                            "name": "JOB_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "SALARY"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "COMMISSION_PCT"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "MANAGER_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "DEPARTMENT_ID"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "EMPLOYEES",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                },
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "EMPLOYEE_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "date",
                                            "name": "START_DATE"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "date",
                                            "name": "END_DATE"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(10)",
                                            "name": "JOB_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "DEPARTMENT_ID"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "JOB_HISTORY",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                },
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "varchar(10)",
                                            "name": "JOB_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(35)",
                                            "name": "JOB_TITLE"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "MIN_SALARY"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "MAX_SALARY"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "JOBS",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                },
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "LOCATION_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(40)",
                                            "name": "STREET_ADDRESS"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(12)",
                                            "name": "POSTAL_CODE"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(30)",
                                            "name": "CITY"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "STATE_PROVINCE"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "char(2)",
                                            "name": "COUNTRY_ID"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "LOCATIONS",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                },
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "REGION_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "REGION_NAME"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "REGIONS",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                }
                            ],
                            "views": [
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "[employee_id]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(10)",
                                            "name": "[job_id]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "[manager_id]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "[department_id]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "[location_id]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "char(2)",
                                            "name": "[country_id]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(20)",
                                            "name": "[first_name]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "[last_name]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "[salary]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "[commission_pct]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(30)",
                                            "name": "[department_name]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(35)",
                                            "name": "[job_title]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(30)",
                                            "name": "[city]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "[state_province]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(40)",
                                            "name": "[country_name]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "[region_name]"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "[EMP_DETAILS_VIEW]",
                                    "schemaName": "[dbo]",
                                    "type": "view"
                                }
                            ]
                        }
                    ]
                }
            ],
            "dbLinks": [],
            "dbVendor": "dbvmssql",
            "name": "115.159.xx.xx",
            "queries": [
                {
                    "database": "DWDB",
                    "groupName": "",
                    "name": "SPREPORTER_COMPARE_OVERVIEW_BATCHUNIQUEHASHINFO",
                    "schema": "READTRACE",
                    "sourceCode": "\r\ncreate procedure ReadTrace.spReporter_Compare_Overview_BatchUniqueHashInfo\r\nas\r\nbegin\r\n\tset nocount on\r\n\r\n\tselect 'Matching' as [Desc],\r\n\t\t\tcount_big(*) as [Count]\r\n\t\t\tfrom ReadTrace.tblUniqueBatches b\r\n\t\t\tinner join ReadTraceCompare.tblUniqueBatches c\r\n\t\t\t\ton b.HashID = c.HashID\r\n\t\r\n\tunion all\r\n\t\tselect 'BO' as [Desc],\r\n\t\t\t\tcount_big(*) \r\n\t\t\t\tfrom ReadTrace.tblUniqueBatches b\r\n\t\t\t\tleft outer join ReadTraceCompare.tblUniqueBatches c\r\n\t\t\t\t\ton b.HashID = c.HashID\r\n\t\t\t\twhere c.HashID is NULL\r\n\r\n\tunion all\r\n\t\tselect 'CO' as [Desc],\r\n\t\t\t\tcount_big(*) \r\n\t\t\t\tfrom ReadTrace.tblUniqueBatches b\r\n\t\t\t\tright outer join ReadTraceCompare.tblUniqueBatches c\r\n\t\t\t\t\ton b.HashID = c.HashID\r\n\t\t\t\twhere b.HashID is NULL\r\n\torder by [Desc]\r\nend ",
                    "type": "procedure"
                }
            ],
            "supportsCatalogs": true,
            "supportsSchemas": true
        }
    ]
}

Sample Indication

The above sample has the same structure as

  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
{
    "createTime":"", //export time
    "createdBy":"sqlflow-exporter",//name of the export tool
    "physicalInstance":"",//server address
    "servers":[
        {
            "name":"",//server name
            "dbVendor":"",//database type，possible values are: dbvathena,dbvazuresql,dbvbigquery,dbvcouchbase,dbvdb2,dbvgreenplum,dbvhana,dbvhive,dbvimpala,dbvinformix,dbvmdx,dbvmysqldbvnetezza,dbvopenedge,dbvoracle,dbvpostgresql,dbvpresto,dbvredshift,dbvsnowflake,dbvsparksql,dbvmssql,dbvsybase,dbvteradata,dbvvertica
            "supportsCatalogs":true,// has database layer
            "supportsSchemas":true,// has schema layer
            "databases":[ // No extra db unit field outside the database list because all units belong to the specific schema and database.
                {
                    "name":"",//database name
                    "schemas":[
                        {
                            "name":"",//schema name
                            "synonyms":[
                                {
                                    "name":"",
                                    "sourceName":"",
                                    "sourceSchema":"",
                                    "sourceDbLinkName":""
                                }
                            ],
                            "sequences":[
                                {
                                    "name":"",
                                    "incrementBy":""
                                }
                            ],
                            "tables":[
                                {
                                    "name":"",//table name 
                                    "columns":[
                                        {
                                            "dataType":"",//column data type
                                            "name":"",//column name
                                            "comment":""//column comment
                                        }
                                    ]
                                }
                            ],
                            "views":[ 
                              {
                                    "name":"",//view name
                                    "columns":[
                                        {
                                            "dataType":"",
                                            "name":"",
                                            "comment":""
                                        }
                                    ]
                                }
                            ],
                            "others":[//others, including resultset, variable, path etc 
                                {
                                    "name":"",
                                    "columns":[
                                        {
                                            "dataType":"",
                                            "name":"",
                                            "comment":""
                                        }
                                    ]
                                }
                            ],
                            "packages":[
                                {
                                    "name":"",//package name 
                                    "procedures":[//prcedures in the package
                                    ],
                                    "functions":[//functions in the package
                                    ],
                                    "triggers":[//triggers in the package
                                    ]
                                }
                            ],
                            "procedures":[
                                {
                                    "name":"",
                                    "type":"",
                                    "arguments":[
                                        {
                                            "name":"",
                                            "dataType":"",
                                            "inout":""
                                        }
                                    ]
                                }
                            ],
                            "functions":[
                                {
                                    "name":"",
                                    "type":"",
                                    "arguments":[
                                        {
                                            "name":"",
                                            "dataType":"",
                                            "inout":""
                                        }
                                    ]
                                }
                            ],
                            "triggers":[
                                {
                                    "name":"",
                                    "type":"",
                                    "arguments":[
                                        {
                                            "name":"",
                                            "dataType":"",
                                            "inout":""
                                        }
                                    ]
                                }
                            ]
                        }
                    ],
            "dbLinks":[
                {
                    "owner":"",
                    "name":"",
                    "userName":"",
                    "host":""
                }
            ],
            "queries":[//DDL scripts in database
                {
                    "database":"",
                    "schema":"",
                    "name":"",
                    "type":"",
                    "sourceCode":"",
                    "groupName":""
                }
            ]
        }
    ],
    "errorMessages":[//errors during the exporting 
        {
            "errorMessage":"",
            "errorType":"",
            "file":""
        }
    ]
  }

Get More Details

You can refer to this section to understand more about what does each field mean.