@veelit/sql-db-analyzer

SQL Database Analyzer

The Sql Database Analyzer tool enables you to analyze an SQL Database and obtain its Functions, Table Schemas, Stored Procedures and Data in JSON format.

It currently supports Microsoft SQL Server Databases with plans to support Postgres SQL and SQLite in the pipeline

Installation

npm install @veelit/sql-db-analyzer

or

yarn add @veelit/sql-database-analyzer

Usage

// ESM Modules
import { MSSQLAnalyzer } from "@veelit/sql-db-analyzer";
import { resolve } from "path";
import dotEnv from "dotenv";

const envPath = resolve(__dirname, "./.env");
dotEnv.config({ path: envPath });

(async () => {
  const options = {
    connection: {
      host: process.env.DB_HOST,
      port: process.env.DB_PORT, // defaults to 1386
      user: process.env.DB_USER,
      pass: process.env.DB_PASS,
      database: process.env.DB_NAME,
    },
    saveTo: "./analysis",
  };

  await new MSSQLAnalyzer(options).run();
})();

Getting a Data Dump

Ensure this SP is available in your MSSQL database

CREATE PROCEDURE [dbo].[A_SP_GET_TABLE_DATA] (
	@tableName SYSNAME
)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @result NVARCHAR(max);
    DECLARE @DynamicSQL NVARCHAR(max);

    SET @DynamicSQL = N'SELECT * FROM [' + @tableName + '] FOR JSON AUTO';
    EXECUTE sp_executesql @DynamicSQL
	PRINT @DynamicSQL;
END

Output