CODEWITHSUNDEEP
sqlsql-serverssmsssms-2014
Julián
Julián

Reputation: 1396

Is it possible to replace words that are not in comments in SQL Server Management Studio 2014?

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 :)

Upvotes: 0

Views: 215

Answers (1)

Anton
Anton

Reputation: 2882

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.

Upvotes: 0

Related Questions