0

I know that in SQL Server Management Studio 2014 using Ctrl + H allows me to replace one word with another.

I have a database script (creation of tables, procedures, views, etc.) that are approximately 150000 lines of code, in this script I have added some extra comments that contain a word that I DO NOT want to remove from comments but I want to remove T-SQL code, for example:

-- Comment [SuperSec].[dbo].[tableA]

SELECT X.*
FROM [SuperSec].[dbo].[tableA] X
WHERE X.Id = '0';

I want it to look like this:

-- Comment [SuperSec].[dbo].[tableA]

SELECT X.*
FROM [dbo].[tableA] X
WHERE X.Id = '0';

Is it possible to do this using Regex or something similar?

UPDATE:

I did not say this at the beginning but this word corresponds to the name of the database, therefore it is referenced in many parts, not only in a FROM, also in a function, an EXEC of a procedure, a subquery, part of a SELECT , in many places.

Thanks :)

Julián
  • 1,238
  • 1
  • 12
  • 24
  • 2
    I would think a regex replace could do it. Have you tried? – Tab Alleman Sep 09 '19 at 13:50
  • 4
    You can replace `FROM [SuperSec].` with `FROM `. – Gordon Linoff Sep 09 '19 at 13:53
  • @Tab Alleman I'm not good with regex, but I'm searching the internet, so far nothing works – Julián Sep 09 '19 at 13:54
  • 1
    That does assume there there won't be `JOIN [SuperSec]...` anywhere in the OP's data, @GordonLinoff. – Thom A Sep 09 '19 at 13:58
  • @Gordon Linoff It is referenced in a FROM, worse also in EXEC, in subqueries, WHERE, UPDATE, etc. Should I apply your solution with all these possibilities? I was curious if there was a way to do it faster. – Julián Sep 09 '19 at 14:00
  • I don't think SSMS is the best tool for this. If you had Notepad++ (for example) then you could probably use regex search/ replace to do what you need. Here's an example of how to ignore comments (not a SQL example, but it should be easy enough to adapt it?): https://notepad-plus-plus.org/community/topic/14118/search-text-in-source-code-excluding-comments – Richard Hansell Sep 09 '19 at 14:09
  • SSMS supports REGEX replacement, @RichardHansell – Thom A Sep 09 '19 at 14:09
  • @Larnu, agreed, but prior to SSMS 2017 it wasn't regular regular expressions, it was non-standard REGEX, which is a bit of a weird choice (and one I'm glad they revisited). OP is on SSMS 2014. – Richard Hansell Sep 09 '19 at 14:17
  • 1
    Considering that SSMS (2017) is free, I don't see a reason why the OP doesn't upgrade though, @RichardHansell :) – Thom A Sep 09 '19 at 14:36

1 Answers1

0

One of the ways to achieve that is to do that programmatically:

0) Put all your code to some NVARCHAR(MAX) column

1) (*see below comment) Find all pieces of commented code and replace your word ("[SuperSec].") with some unique tag like #$UNIQUETAG$#

2) Then replace your word ("[SuperSec].") with empty string in all remaining code

3) Finally, replace #$UNIQUETAG$# back with your initial word ("[SuperSec].")

So the only problem is Step 1 - to find the commented code. If all the comments follow "-- ..." format and there are no "/* ... */" comments, it's not difficult to find it - it is anything between "--" and end of line (CHAR(13)+CHAR(10))

If you don't want to do programming... there are some solutions that you can adapt, e.g. Remove all comments from a text in T-SQL which finds and removes all comments. You just need to change its logic so instead of removing comments, it replaces your word in a comment with unique tag.

Anton
  • 2,846
  • 1
  • 10
  • 15