Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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

前提

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

手順

1. ApexSQL Refactorのインストール

ApexSQL公式サイトからApexSQL Refactorをダウンロード後、ApexSQLRefactor.exeを実行するとインストールの設定が始まります。
Host integrationの画面で自分が整形ツールを導入したい製品を選択してインストールしてください。
image.png
インストールが完了した後、対象のアプリケーションを再起動すると使える状態になっています。

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

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

参考までに自分が作成した設定ファイルです。(※随時更新予定)
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

zozotech
70億人のファッションを技術の力で変えていく
https://tech.zozo.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした