sqld - Microsoft SQL Schema Extraction Tool

A Windows command-line tool to extract the database schema from two databases so that the definitions can be compared for differences.

sql - Microsoft SQL Schema Extraction Tool

Motivation

I started working at a new organisation which had database schema issues. There was drift between the pre-production (UAT) and production databases, causing confidence issues when deploying updates. The databases had hundreds of stored procedures, none of which were registered in the source code control system. They only existed in the database.

While the team used Redgate’s sql compare product the databases still encountered drift. So, I wanted to write a tool to extract as much of the database definitions to discrete files within two sub folders, with one folder for the UAT database definition and one for the production definition. The resulting files allowed us to use the excellent WinMerge tool to compare and find differences between the two databases.

Once differences are reviewed and conflicts fixed, all the stored procedures and user defined functions could be committed to the Git repository.

Schema Definitions Extracted

Stored procedure definitions
Table field definitions, including data type and data length
Table index definitions
User-defined functions

Encrypt User Password

Before you start, you need to encrypt the password for the username you configure in the settings.ini file. Use the -password= flag and supply your password, and an AES-encrypted password will be displayed within square brackets.

1./sqld -password=BishBashBosh
2
3AES encrypted password = [3bd742ec11 ... e87dff1e]

Settings.ini

You can configure the software by editing the settings.ini. You must add the Microsoft SQL Server names where the production and pre-production (uat) databases live. The databases need to have the same name within both servers.

1server_prod   = "GCLIN05"
2server_uat    = "GCLIN05UAT"
3database_name = "Diadem2"
4username      = "NUTH\BradleyP6_adm"
5password      = "3bd742ec11 ... e87dff1e"

The username will be your Windows Active Directory account. This user will need admin permissions to both databases for the extraction tool to work correctly.

Extracting the database schema

To run the extraction against the databases configured in the settings.ini file, run the executable with no parameters like:

1./sqld

Running sqld will produce the following folder structure. Each sub-folder of the sqld folder will contain all the necessary .SQL files extracted from the database.

1.
2├── sqld
3│   ├── production
4│   └── uat

Download

The ZIP file contains the Windows 64bit executable sqld.exe

Language

Written and compiled in Go 1.23.0

Source Code Availability

None at the moment. Later, it could be released under the MIT license.

Future Plans

Ability to define a list of tables which hold configuration data, so that the sqld tool can generate a script to rollback the production configuration to your UAT database.

Version History

1.02 [15.11.2024] – Added AES password encryption and fixed table index definition extraction.
1.01 [01.11.2024] – Initial release.