203 lines
6.6 KiB
JavaScript
203 lines
6.6 KiB
JavaScript
// @ts-nocheck
|
|
/**
|
|
* extract_mssql_defs_to_files.js
|
|
*
|
|
* Connects to a MSSQL database, for each user table:
|
|
* 1. Extracts the CREATE TABLE DDL.
|
|
* 2. Selects the TOP 10 rows by primary key DESC (or just TOP 10 if no PK).
|
|
* 3. Writes both the DDL and the rows (as INSERT statements) into
|
|
* tables/<schema>.<table>.sql
|
|
*
|
|
* Usage:
|
|
* npm install mssql
|
|
* node extract_mssql_defs_to_files.js
|
|
*/
|
|
|
|
const sql = require('mssql');
|
|
const fs = require('fs');
|
|
const path = require('path');
|
|
|
|
// --- CONFIGURE YOUR DATABASE CONNECTION HERE ---
|
|
const config = {
|
|
user: 'sa',
|
|
password: 'sa_tekno23',
|
|
server: '10.10.10.3',
|
|
database: 'eazybusiness',
|
|
options: { encrypt: false, trustServerCertificate: true }
|
|
};
|
|
|
|
async function ensureDir(dir) {
|
|
if (!fs.existsSync(dir)) {
|
|
fs.mkdirSync(dir, { recursive: true });
|
|
}
|
|
}
|
|
|
|
function escapeSqlString(val) {
|
|
if (val === null || val === undefined) return 'NULL';
|
|
if (typeof val === 'number') return val;
|
|
if (typeof val === 'boolean') return val ? 1 : 0;
|
|
return "'" + String(val).replace(/'/g, "''") + "'";
|
|
}
|
|
|
|
async function getTables(pool) {
|
|
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
|
|
`);
|
|
return tables.recordset;
|
|
}
|
|
|
|
async function getColumns(pool, object_id) {
|
|
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
|
|
`);
|
|
return columns.recordset;
|
|
}
|
|
|
|
async function getPrimaryKey(pool, object_id) {
|
|
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
|
|
`);
|
|
return pk.recordset;
|
|
}
|
|
|
|
function buildCreateTable(schema_name, table_name, columns, pk) {
|
|
let createStmt = `CREATE TABLE [${schema_name}].[${table_name}] (\n`;
|
|
createStmt += columns.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.length > 0) {
|
|
const pkCols = pk.map(r => `[${r.column_name}]`).join(', ');
|
|
createStmt += `,\n CONSTRAINT [${pk[0].pk_name}] PRIMARY KEY (${pkCols})`;
|
|
}
|
|
createStmt += '\n);\n\n';
|
|
return createStmt;
|
|
}
|
|
|
|
async function getLatestRows(pool, schema_name, table_name, pkCols, columns) {
|
|
let orderBy = '';
|
|
if (pkCols.length > 0) {
|
|
orderBy = pkCols.map(col => `[${col}] DESC`).join(', ');
|
|
}
|
|
const colList = columns.map(c => `[${c.column_name}]`).join(', ');
|
|
const query = `
|
|
SELECT TOP 10 ${colList}
|
|
FROM [${schema_name}].[${table_name}]
|
|
${orderBy ? `ORDER BY ${orderBy}` : ''}
|
|
`;
|
|
try {
|
|
const rows = await pool.request().query(query);
|
|
return rows.recordset;
|
|
} catch (e) {
|
|
// Table may be empty or inaccessible
|
|
return [];
|
|
}
|
|
}
|
|
|
|
function buildInsertStatements(schema_name, table_name, columns, rows) {
|
|
if (!rows || rows.length === 0) return '-- No data rows found\n';
|
|
let inserts = '-- Latest 10 rows:\n';
|
|
const colNames = columns.map(c => `[${c.column_name}]`).join(', ');
|
|
for (const row of rows) {
|
|
const vals = columns.map(c => escapeSqlString(row[c.column_name])).join(', ');
|
|
inserts += `INSERT INTO [${schema_name}].[${table_name}] (${colNames}) VALUES (${vals});\n`;
|
|
}
|
|
return inserts;
|
|
}
|
|
|
|
async function getViews(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;
|
|
}
|
|
|
|
async function getProcedures(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;
|
|
}
|
|
|
|
async function main() {
|
|
await ensureDir(path.join(__dirname, 'tables'));
|
|
let pool;
|
|
try {
|
|
pool = await sql.connect(config);
|
|
const tables = await getTables(pool);
|
|
|
|
// TABLES
|
|
for (const t of tables) {
|
|
const { object_id, schema_name, table_name } = t;
|
|
const columns = await getColumns(pool, object_id);
|
|
const pk = await getPrimaryKey(pool, object_id);
|
|
const pkCols = pk.map(r => r.column_name);
|
|
|
|
const ddl = buildCreateTable(schema_name, table_name, columns, pk);
|
|
const rows = await getLatestRows(pool, schema_name, table_name, pkCols, columns);
|
|
const inserts = buildInsertStatements(schema_name, table_name, columns, rows);
|
|
|
|
const fileName = `${schema_name}.${table_name}.sql`;
|
|
const outPath = path.join(__dirname, 'tables', fileName);
|
|
fs.writeFileSync(outPath, ddl + inserts, 'utf8');
|
|
console.log('Written:', fileName);
|
|
}
|
|
|
|
// VIEWS
|
|
const views = await getViews(pool);
|
|
for (const v of views) {
|
|
const { schema_name, view_name, definition } = v;
|
|
const viewDef = `CREATE VIEW [${schema_name}].[${view_name}] AS\n${definition}\nGO\n`;
|
|
const fileName = `${schema_name}.${view_name}.sql`;
|
|
const outPath = path.join(__dirname, 'tables', fileName);
|
|
fs.writeFileSync(outPath, viewDef, 'utf8');
|
|
console.log('Written:', fileName);
|
|
}
|
|
|
|
// PROCEDURES
|
|
const procs = await getProcedures(pool);
|
|
for (const p of procs) {
|
|
const { schema_name, proc_name, definition } = p;
|
|
const procDef = `CREATE PROCEDURE [${schema_name}].[${proc_name}]\nAS\n${definition}\nGO\n`;
|
|
const fileName = `${schema_name}.${proc_name}.sql`;
|
|
const outPath = path.join(__dirname, 'tables', fileName);
|
|
fs.writeFileSync(outPath, procDef, 'utf8');
|
|
console.log('Written:', fileName);
|
|
}
|
|
|
|
await pool.close();
|
|
console.log('Done.');
|
|
} catch (err) {
|
|
if (pool) await pool.close();
|
|
console.error('Error:', err);
|
|
}
|
|
}
|
|
|
|
main();
|