sql-schema-reader

Read Sql Server database schema.

Downloads in past

Stats

StarsIssuesVersionUpdatedCreatedSize
sql-schema-reader
303.4.17 years ago7 years agoMinified + gzip package size for sql-schema-reader in KB

Readme

SqlSchemaReader
Read SQL Server schema information. Uses Tedious.js.

install

``` npm install sql-schema-reader ```

Usage

```javascript const schemaReader = require("sql-schema-reader"); async function go(){
const config = {
"server":"server",
"database": "dbName",
"username":"",
"password":""  
};
// Tables
const tableNames = await schemaReader.tableNames(config);
console.log(tableNames);
const table = await schemaReader.table(config, "tableName");
console.log(table);
const tables = await schemaReader.tables(config, ["tableName1", "tableName2"]);
console.log(tables);
// Stored Procedures
const procNames = await schemaReader.storedProcedureNames(config);
console.log(procNames);
const proc = await schemaReader.storedProcedure(config, "procName");
console.log(proc);
const procs = await schemaReader.storedProcedures(config, ["procName1", "procName2"]);
console.log(procs);
const procDefinition = await schemaReader.functionDefinition(config, "procName");
console.log(procDefinition);
// Table Value Functions
const functionNames = await schemaReader.tableValueFunctionNames(config);
console.log(functionNames);
const func = await schemaReader.tableValueFunction(config, "functionName");
console.log(func);
const funcs = await schemaReader.tableValueFunctions(config, ["functionName1", "functionName2"]);
console.log(funcs);
const functionDefinition = await schemaReader.functionDefinition(config, "functionName");
console.log(functionDefinition);
// Scalar Functions
const scalarNames = await schemaReader.scalarFunctionNames(config);
console.log(scalarNames);
const scalar = await schemaReader.scalarFunction(config, "scalarName");
console.log(scalar);
const scalars = await schemaReader.scalarFunctions(config, ["scalarName1", "scalarName2"]);
console.log(scalars);
const scalarDefinition = await schemaReader.functionDefinition(config, "scalarName");
console.log(scalarDefinition);
} go(); ```

Outputs

```javascript // tableNames
{ 
"database": "dbName", 
"schema": "dbo", 
"name": "tableName" 
}
// table {
"database": "dbName",
"schema": "dbo",
"name": "tableName",
"tableType": "BASE TABLE",
"columns": [
{
"ordinal": 1,
"columnName": "columnName",
"isNullable": false,
"typeName": "nvarchar",
"maxLength": 8,
"precision": 0,
"default": "",
"dateTimePrecision": 0,
"scale": 0,
"isIdentity": false,
"isStoreGenerated": false,
"primaryKey": true,
"isFixedLength": false,
"hasPrecisionAndScale": false,
"isRowVersion": false
}
],
"primaryKeys": [
{
"ordinal": 1,
"columnName": "columnName",
"isNullable": false,
"typeName": "nvarchar",
"maxLength": 8,
"precision": 0,
"default": "",
"dateTimePrecision": 0,
"scale": 0,
"isIdentity": false,
"isStoreGenerated": false,
"primaryKey": true,
"isFixedLength": false,
"hasPrecisionAndScale": false,
"isRowVersion": false
}
],
"foreignKeys": [
{
"pkTableName": "tableName2",
"constraintName": "FK_tableName_tableName2",
"columns": [
{
"fkColumn": {
"ordinal": 1,
"columnName": "columnName",
"isNullable": false,
"typeName": "nvarchar",
"maxLength": 64,
"precision": 0,
"default": "",
"dateTimePrecision": 0,
"scale": 0,
"isIdentity": false,
"isStoreGenerated": false,
"primaryKey": false,
"isFixedLength": false,
"hasPrecisionAndScale": false,
"isRowVersion": false
},
"pkColumn": {
"ordinal": 1,
"columnName": "columnName",
"isNullable": false,
"typeName": "nvarchar",
"maxLength": 64,
"precision": 0,
"default": "",
"dateTimePrecision": 0,
"scale": 0,
"isIdentity": false,
"isStoreGenerated": false,
"primaryKey": true,
"isFixedLength": false,
"hasPrecisionAndScale": false,
"isRowVersion": false
},
"ordinal": 1
}
],
"relationship": "manyToOne"
}
],
"reverseNavigations": [
{
"fkTableName": "tableName3",
"constraintName": "FK_tableName3_tableName",
"columns": [
{
"fkColumn": {
"ordinal": 1,
"columnName": "columnName",
"isNullable": false,
"typeName": "nvarchar",
"maxLength": 64,
"precision": 0,
"default": "",
"dateTimePrecision": 0,
"scale": 0,
"isIdentity": false,
"isStoreGenerated": false,
"primaryKey": false,
"isFixedLength": false,
"hasPrecisionAndScale": false,
"isRowVersion": false
},
"pkColumn": {
"ordinal": 1,
"columnName": "columnName",
"isNullable": false,
"typeName": "nvarchar",
"maxLength": 64,
"precision": 0,
"default": "",
"dateTimePrecision": 0,
"scale": 0,
"isIdentity": false,
"isStoreGenerated": false,
"primaryKey": true,
"isFixedLength": false,
"hasPrecisionAndScale": false,
"isRowVersion": false
},
"ordinal": 1
}
],
"relationship": "manyToOne"
}
]
} // tables: Array of Tables // tableValueFunctionNames
{ 
"database": "dbName",
"schema": "dbo",
"name": "tvFunctionName" 
}
// tableValueFunction {
"database": "dbName",
"schema": "dbo",
"name": "tvFunctionName",
"parameters": [
{
"ordinal": 1,
"parameterMode": "IN",
"parameterName": "@parmName",
"dataType": "nchar",
"maxLength": 2,
"precision": 0,
"scale": 0,
"datetimePrecision": 0
}
],
"returnColumns": [
{
"name": "colName1",
"dataType": "int"
},
{
"name": "colName2",
"dataType": "nvarchar",
"maxLength": 4000
}
]
} // tableValueFunctions: Array of table value functions // storedProcedureNames
{ 
"database": "dbName",
"schema": "dbo",
"name": "storedProcedureName" 
}
// storedProcedure {
"database": "dbName",
"schema": "dbo",
"name": "storedProcedureName",
"parameters": [
{
"ordinal": 1,
"parameterMode": "IN",
"parameterName": "@parmName",
"dataType": "nchar",
"maxLength": 2,
"precision": 0,
"scale": 0,
"datetimePrecision": 0
}
],
"returnColumns": [
{
"name": "colName1",
"dataType": "int"
},
{
"name": "colName2",
"dataType": "nvarchar",
"maxLength": 4000
}
]
} // storedProcedures: Array of stored procedures // scalarFunctionNames
{ 
"database": "dbName",
"schema": "dbo",
"name": "scalarFunctionName" 
}
// scalarFunction {
"database": "dbName",
"schema": "dbo",
"name": "scalarFunctionName",
"parameters": [
{
"ordinal": 1,
"parameterMode": "IN",
"parameterName": "@parmName",
"dataType": "nchar",
"maxLength": 2,
"precision": 0,
"scale": 0,
"datetimePrecision": 0
}
],
"returnDataType": "varchar"
} // scalarFunctions: Array of scalar functions ```

Schema Read Error

Sometimes the metadata of return columns for stored procedures cannot be read. The output will have a schemaReadError when this happens. Known causes:
  • When a stored procedure uses a #TempTable, the return columns cannot be read.
  • When a stored procedure is invalid, for example a table does not exist.
```javascript {
"database": "dbName",
"schema": "dbo",
"name": "storedProcedureName",
"parameters": [],
"schemaReadError" = {
message: "SQL Schema Reader error message.",
error: originalErrorResponse
}
} ```