0

I have a file with thousand lines of codes with index details like below:

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U'))  
BEGIN  
 CREATE UNIQUE INDEX [IX_Test1] ON [Test1]  
(  
[cd] ASC   
)   
 WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,  DROP_EXISTING = OFF ) ON [PRIMARY]  
END  


IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test2' AND [object_id] = OBJECT_ID(N'Test2', N'U'))  
BEGIN  
 CREATE INDEX [IX_Test2] ON [Test2]  
(  
[AdID] ASC , [Date] ASC   
)  INCLUDE ( [AID], [LM] )   
 WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,   DROP_EXISTING = OFF ) ON [PRIMARY]  
END

I want to format this data like below for comparison purposes:

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U'))  BEGIN   CREATE UNIQUE INDEX [IX_Test1] ON [Test1]  (  [cd] ASC   )    WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,  DROP_EXISTING = OFF ) ON [PRIMARY]  END   
IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test2' AND [object_id] = OBJECT_ID(N'Test2', N'U'))  BEGIN   CREATE INDEX [IX_Test2] ON [Test2]  (  [AdID] ASC , [Date] ASC   )  INCLUDE ( [AID], [LM] )    WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,   DROP_EXISTING = OFF ) ON [PRIMARY]  END

So basically everything between IF and END should come as one line. I tried to do it using REGEX but didn't get it work. If someone could help me in this regard, it would be of great help.

Please let me know if you need any further details.

2

If you are willing to do this in two step, here is an approach that works, using the Regex option in SSMS's Replace dialog.

Step 1

Delete all line endings, squashing it all into a single line.

Replace: \r\n

With:

Explanation: The \r\n looks for a line ending, which is almost certainly a Carriage Return (\r) followed by a New Line (\n). Replace it with nothing.

Step 2

Replace all occurrences of "END" with "END" plus a new line ending.

Replace: END

With: END\r\n

Explanation: Replace "END" with "END" plus a carriage return plus a new line.

Oddity

Your example above has two spaces after the first "END", and no spaces after the second one. This will cause the results after Step 2 to look something like this:

IF NOT EXISTS...
  IF NOT EXISTS...

If your real code also has that oddity, you can fix that by ensuring that you don't have spaces after "END", with a Step 0 of:

Replace: END (two spaces after "END")

With: END (no spaces after "END")

Tested in SSMS 17.9.1.

  • Thank you for your help! It saved lot of my hours to do manual comparision. Much appreciated! – SQLPRODDBA May 14 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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