Flash Tip - Automate Redgate SQL Server Formatting using Git Pre-Commit Hooks
Ctrl-K + Ctrl-Y. It saves so much time by formatting your SQL code before it gets shipped off for Code Review. The frustrating part is when you double-check your code in the Pull Request and you realize you forgot to hit those hotkeys.
With this Hook, that will never happen ever again.
What You Need:
- Redgate SQL Toolbelt Essentials / Redgate SQL Format
- Git
- A Repository with SQL Files to format
First, check C:\Program Files (x86)\Red Gate\SQL Prompt 10 to see if SqlPrompt.Format.CommandLine.exe exists:
Next, we want to add that path as an Environment Variable:
Inside of Powershell, we want to type this command and execute it:
SqlPrompt.Format.CommandLine.exe
If you execute that command and you see something similar to the image below, then everything is going swimmingly:
Now we want to go into the .git\hooks folder of your repository and copy and paste pre-commit.sample in the same folder. Rename that pasted file to pre-commit (no file extension).
We want to edit the pre-commit file so it executes SqlPrompt.Format.CommandLine.exe with default styling on the root folder in your repository. So let's open up that file and add these lines to it:
#!/bin/sh
SqlPrompt.Format.CommandLine.exe --i-agree-to-the-eula --path $PWD --style Default
git add .
With those commands inside of the pre-commit file, every time you run:
git commit -m <insert comment>
All of your SQL Files will be formatted to the default SQL Format style!
BONUS - It also works with Redgate SQL Source Control!
That's right, Pre-Commit Hooks also integrate with SQL Server Management Studio. Let's say we have a badly formatted Stored Procedure for a Database like the example down below:
Then we add the Stored Procedure and click on the Commit button. The Pre-Commit Hook will execute and the Stored Procedure will have Redgate SQL Format applied to it in your repository:
Note: If you want the formatted Stored Procedure to be on the SQL Server Database, you will need to use SQL Change Automation Sync from your Git repository to the Database. This can run as part of your Automated CI/CD process.
If you have any questions, comments or suggestions, please feel free to drop a response to this article.
Until then, I'll see you next time.