130 lines
4.5 KiB
JavaScript
130 lines
4.5 KiB
JavaScript
// Node.js script to extract table, view, and procedure definitions from a MSSQL database
|
|
// Requires: npm install mssql
|
|
// Fill in your connection details below
|
|
|
|
const sql = require('mssql');
|
|
|
|
const config = {
|
|
user: 'sa',
|
|
password: 'sa_tekno23',
|
|
server: '10.10.10.3', // e.g. 'localhost'
|
|
database: 'eazybusiness',
|
|
options: {
|
|
encrypt: false, // Set to true if using Azure
|
|
trustServerCertificate: true // For local dev/testing
|
|
}
|
|
};
|
|
|
|
async function getTableDefinitions(pool) {
|
|
// Get table columns, PKs, and constraints
|
|
const tables = await pool.request().query(`
|
|
SELECT t.object_id, s.name AS schema_name, t.name AS table_name
|
|
FROM sys.tables t
|
|
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
|
|
ORDER BY s.name, t.name
|
|
`);
|
|
|
|
let results = [];
|
|
for (const row of tables.recordset) {
|
|
const { object_id, schema_name, table_name } = row;
|
|
|
|
// Get columns
|
|
const columns = await pool.request().query(`
|
|
SELECT c.name AS column_name,
|
|
TYPE_NAME(c.user_type_id) AS data_type,
|
|
c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity
|
|
FROM sys.columns c
|
|
WHERE c.object_id = ${object_id}
|
|
ORDER BY c.column_id
|
|
`);
|
|
|
|
// Get primary key
|
|
const pk = await pool.request().query(`
|
|
SELECT k.name AS pk_name, c.name AS column_name
|
|
FROM sys.key_constraints k
|
|
INNER JOIN sys.index_columns ic ON k.parent_object_id = ic.object_id AND k.unique_index_id = ic.index_id
|
|
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
|
|
WHERE k.parent_object_id = ${object_id} AND k.type = 'PK'
|
|
ORDER BY ic.key_ordinal
|
|
`);
|
|
|
|
// Compose CREATE TABLE statement
|
|
let createStmt = `CREATE TABLE [${schema_name}].[${table_name}] (\n`;
|
|
createStmt += columns.recordset.map(col => {
|
|
let line = ` [${col.column_name}] ${col.data_type}`;
|
|
if (col.data_type.match(/char|binary|text|nchar|nvarchar|varbinary/i) && col.max_length > 0) {
|
|
line += `(${col.max_length === -1 ? 'MAX' : col.max_length})`;
|
|
} else if (col.data_type.match(/decimal|numeric/i)) {
|
|
line += `(${col.precision},${col.scale})`;
|
|
}
|
|
if (col.is_identity) line += ' IDENTITY(1,1)';
|
|
line += col.is_nullable ? ' NULL' : ' NOT NULL';
|
|
return line;
|
|
}).join(',\n');
|
|
if (pk.recordset.length > 0) {
|
|
const pkCols = pk.recordset.map(r => `[${r.column_name}]`).join(', ');
|
|
createStmt += `,\n CONSTRAINT [${pk.recordset[0].pk_name}] PRIMARY KEY (${pkCols})`;
|
|
}
|
|
createStmt += '\n);';
|
|
results.push(createStmt);
|
|
}
|
|
return results;
|
|
}
|
|
|
|
async function getViewDefinitions(pool) {
|
|
const views = await pool.request().query(`
|
|
SELECT s.name AS schema_name, v.name AS view_name, OBJECT_DEFINITION(v.object_id) AS definition
|
|
FROM sys.views v
|
|
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
|
|
ORDER BY s.name, v.name
|
|
`);
|
|
return views.recordset.map(v =>
|
|
`CREATE VIEW [${v.schema_name}].[${v.view_name}] AS\n${v.definition}\nGO`
|
|
);
|
|
}
|
|
|
|
async function getProcedureDefinitions(pool) {
|
|
const procs = await pool.request().query(`
|
|
SELECT s.name AS schema_name, p.name AS proc_name, OBJECT_DEFINITION(p.object_id) AS definition
|
|
FROM sys.procedures p
|
|
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
|
|
ORDER BY s.name, p.name
|
|
`);
|
|
return procs.recordset.map(p =>
|
|
`CREATE PROCEDURE [${p.schema_name}].[${p.proc_name}]\nAS\n${p.definition}\nGO`
|
|
);
|
|
}
|
|
|
|
async function main() {
|
|
try {
|
|
let pool = await sql.connect(config);
|
|
|
|
console.log('--- TABLES ---');
|
|
const tables = await getTableDefinitions(pool);
|
|
tables.forEach(def => {
|
|
console.log(def);
|
|
console.log('GO\n');
|
|
});
|
|
|
|
console.log('--- VIEWS ---');
|
|
const views = await getViewDefinitions(pool);
|
|
views.forEach(def => {
|
|
console.log(def);
|
|
console.log();
|
|
});
|
|
|
|
console.log('--- PROCEDURES ---');
|
|
const procs = await getProcedureDefinitions(pool);
|
|
procs.forEach(def => {
|
|
console.log(def);
|
|
console.log();
|
|
});
|
|
|
|
await pool.close();
|
|
} catch (err) {
|
|
console.error('Error:', err);
|
|
}
|
|
}
|
|
|
|
main();
|