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?

enter image description here

up vote 10 down vote accepted

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:

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.

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. enter image description here

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.