SQL Server Management Studio(SSMS)にクエリ整形ツールを導入した

SSMSはSQL Serverの統合環境ですが、コーディングの整形機能はデフォルトでは備わっていません。
社内のコード規約に沿った整形ツールが欲しいなと思い、ApexSQL Refactorに出会ったのでそちらの導入方法のメモです。

前提

この記事では、SSMS18にインストールしています。

手順

1. ApexSQL Refactorのインストール

ApexSQL公式サイトからApexSQL Refactorをダウンロード後、ApexSQLRefactor.exeを実行するとインストールの設定が始まります。
Host integrationの画面で自分が整形ツールを導入したい製品を選択してインストールしてください。

インストールが完了した後、対象のアプリケーションを再起動すると使える状態になっています。

2. 好きにFormatterの設定を変更

ApexSQLタブの中のOptionsからフォーマットルール等の設定が可能です。

参考までに自分が作成した設定ファイルです。（※随時更新予定）
https://github.com/saitoryuji/ApexSQL_Refactor_Setting/blob/master/ApexSQL%20Refactor/FormattingSettings.xml

結果

かゆいところに手の届かない感も否めませんが、使い心地としては悪くありません。
例として、Microsoftが公開しているコードを、自分のフォーマットルールで整形してみると
これが

sample_before_format.sql

USE [AdventureworksDW2016]
GO


DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON
SELECT s.SalesTerritoryRegion,d.[CalendarYear],FirstName + ' ' + lastName as 'Employee',FORMAT(SUM(f.SalesAmount),'C') AS 'Total Sales', 
SUM(f.OrderQuantity) as 'Order Quantity', COUNT(distinct f.SalesOrdernumber) as 'Number of Orders', 
count(distinct f.Resellerkey) as 'Num of Resellers'
FROM FactResellerSalesXL_PageCompressed f
INNER JOIN [dbo].[DimDate] d ON f.OrderDateKey= d.Datekey
INNER JOIN [dbo].[DimSalesTerritory] s on s.SalesTerritoryKey=f.SalesTerritoryKey
INNER JOIN [dbo].[DimEmployee] e on e.EmployeeKey=f.EmployeeKey
WHERE FullDateAlternateKey between '1/1/2005' and '1/1/2007'
GROUP BY d.[CalendarYear],s.SalesTerritoryRegion,FirstName + ' ' + lastName
ORDER BY SalesTerritoryRegion,CalendarYear,[Total Sales] desc
SET STATISTICS IO OFF

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON
SELECT s.SalesTerritoryRegion,d.[CalendarYear],FirstName + ' ' + lastName as 'Employee',FORMAT(SUM(f.SalesAmount),'C') AS 'Total Sales', 
SUM(f.OrderQuantity) as 'Order Quantity', COUNT(distinct f.SalesOrdernumber) as 'Number of Orders', 
count(distinct f.Resellerkey) as 'Num of Resellers'
FROM FactResellerSalesXL_CCI  f
INNER JOIN [dbo].[DimDate] d ON f.OrderDateKey= d.Datekey
INNER JOIN [dbo].[DimSalesTerritory] s on s.SalesTerritoryKey=f.SalesTerritoryKey
INNER JOIN [dbo].[DimEmployee] e on e.EmployeeKey=f.EmployeeKey
WHERE FullDateAlternateKey between '1/1/2005' and '1/1/2007'
GROUP BY d.[CalendarYear],s.SalesTerritoryRegion,FirstName + ' ' + lastName
ORDER BY SalesTerritoryRegion,CalendarYear,[Total Sales] desc
SET STATISTICS IO OFF

こうなります

smaple_after_format.sql

USE [AdventureworksDW2016];
GO

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;

SELECT 
	   s.SalesTerritoryRegion
	 , d.CalendarYear
	 , FirstName + ' ' + lastName AS 'Employee'
	 , FORMAT(SUM(f.SalesAmount) , 'C') AS 'Total Sales'
	 , SUM(f.OrderQuantity) AS 'Order Quantity'
	 , COUNT(DISTINCT f.SalesOrdernumber) AS 'Number of Orders'
	 , COUNT(DISTINCT f.Resellerkey) AS 'Num of Resellers'
FROM 
	FactResellerSalesXL_PageCompressed AS f
	INNER JOIN dbo.DimDate AS d
		ON f.OrderDateKey = d.Datekey
	INNER JOIN dbo.DimSalesTerritory AS s
		ON s.SalesTerritoryKey = f.SalesTerritoryKey
	INNER JOIN dbo.DimEmployee AS e
		ON e.EmployeeKey = f.EmployeeKey
WHERE FullDateAlternateKey BETWEEN '1/1/2005' AND '1/1/2007'
GROUP BY 
		 d.CalendarYear
	   , s.SalesTerritoryRegion
	   , FirstName + ' ' + lastName
ORDER BY 
		 SalesTerritoryRegion
	   , CalendarYear
	   , [Total Sales] DESC;

SET STATISTICS IO OFF;

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;

SELECT 
	   s.SalesTerritoryRegion
	 , d.CalendarYear
	 , FirstName + ' ' + lastName AS 'Employee'
	 , FORMAT(SUM(f.SalesAmount) , 'C') AS 'Total Sales'
	 , SUM(f.OrderQuantity) AS 'Order Quantity'
	 , COUNT(DISTINCT f.SalesOrdernumber) AS 'Number of Orders'
	 , COUNT(DISTINCT f.Resellerkey) AS 'Num of Resellers'
FROM 
	FactResellerSalesXL_CCI AS f
	INNER JOIN dbo.DimDate AS d
		ON f.OrderDateKey = d.Datekey
	INNER JOIN dbo.DimSalesTerritory AS s
		ON s.SalesTerritoryKey = f.SalesTerritoryKey
	INNER JOIN dbo.DimEmployee AS e
		ON e.EmployeeKey = f.EmployeeKey
WHERE FullDateAlternateKey BETWEEN '1/1/2005' AND '1/1/2007'
GROUP BY 
		 d.CalendarYear
	   , s.SalesTerritoryRegion
	   , FirstName + ' ' + lastName
ORDER BY 
		 SalesTerritoryRegion
	   , CalendarYear
	   , [Total Sales] DESC;

SET STATISTICS IO OFF;

補足

インストールが手間だけど、どんな感じか試したい方はブラウザ上で試せる機能もあるので是非。
https://sql-formatter.online/options/formatting

参照元

お借りしたソースコードのリポジトリとライセンス
https://github.com/microsoft/sql-server-samples
https://github.com/microsoft/sql-server-samples/blob/master/license.txt