Microsoft SQL Server

metadata.json exported by ingester has the same structure as the dbobjs.servers part of the Dlineage tool result

This page gives a sample metadata for Microsoft SQL Server.

Sample

{
    "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

{
    "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.

Last updated