ssms - how to format T-SQL scripts?

Question

as part of the process to add articles to my publication I use the stored procedure sp_addarticle:

use [Returns]
exec sp_addarticle @publication = N'USReturns', @article = N'tblReturnsContainerTypeDescr', @source_owner = N'dbo', @source_object = N'tblReturnsContainerTypeDescr', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tblReturnsContainerTypeDescr', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotblReturnsContainerTypeDescr]', @del_cmd = N'CALL [sp_MSdel_dbotblReturnsContainerTypeDescr]', @upd_cmd = N'SCALL [sp_MSupd_dbotblReturnsContainerTypeDescr]'
GO

I have publications with over hundreds of articles. I save the scripts, but I would like them to be properly formatted. How can I format my scripts in SSMS?

I wanted to see my scripts in this way:

use [Returns]
exec sp_addarticle 
 @publication = N'USReturns', 
 @article = N'tblReturnsContainerTypeDescr', 
 @source_owner = N'dbo', 
 @source_object = N'tblReturnsContainerTypeDescr', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x000000000803509F, 
 @identityrangemanagementoption = N'none', 
 @destination_table = N'tblReturnsContainerTypeDescr', 
 @destination_owner = N'dbo', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_dbotblReturnsContainerTypeDescr]', 
 @del_cmd = N'CALL [sp_MSdel_dbotblReturnsContainerTypeDescr]', 
 @upd_cmd = N'SCALL [sp_MSupd_dbotblReturnsContainerTypeDescr]'
GO

what is working for me at the moment, is copying and pasting the script into word then following this link: Special characters you can use with Find and Replace in Word

I can put all the "@" in a new line and other similar tricks:

Is there any way of doing this level of formatting using SSMS?

Answer 1

SSMS doesn't have any built-in formatting functions, but there are several 3rd party tools and web sites that can help, many of them free, and all of them surely better than using Word:

• http://www.red-gate.com/products/sql-development/sql-prompt/
• http://www.apexsql.com/sql_tools_refactor.aspx
• http://www.dpriver.com/pp/sqlformat.htm
• http://www.cleancss.com/sql-beautify/
• http://www.sql-format.com/
• http://poorsql.com/
• http://www.freeformatter.com/sql-formatter.html

This is just a brief subset from the first page of a search result. I am sure there are dozens of others, but I don't want to turn this into a shopping list question - the main thrust is that you need to pick a tool that you like, rather than hoping SSMS can do it directly.

Answer 2

T-SQL Formatting with some customization and relatively comprehensive results can be obtained for free with a couple of clicks in Notepad++. Select the plugins menu, select Plugin Manager, press "P", install and customize Poor Man's T-SQL Formatter. Now copy any code in, select Languages -> S -> SQL then Plugins -> Poor Man's T-SQL Formatter -> Format T-SQL Code.

If you do not want to change the language every time simply copy paste your new code into the same editor window.

NotePad++ also has Regex support and a ton of other useful features. I'm not affiliated, just a happy user.