Cleaning up a SQL script after SQL Compare or SQL Data Compare
Published 21 August 2019
If you have produced a SQL script and you want to edit it by removing lines relating to certain databases or users for example, then you can use a simple DOS command to do all the tricky work for you in one line.
Create a DOS batch file the contents being as follows:
findstr /v <string to be removed> <inputfile> > temp.txt move /y temp.txt <inputfile>
So to remove any lines containing 'sp_addrolemember' in a file called test1.sql the batch file will contain:
findstr /v sp_addrolemember test1.sql > temp.txt move /y temp.txt test1.sql
Therefore if test1.sql starts off containing these lines:
EXEC sp_addrole N'roleMCEStore' EXEC sp_addrolemember N'roleMCEStore',N'PROD\svcMCEStore' GO
Run the batch file and test1.sql will now contain:
EXEC sp_addrole N'roleMCEStore' GO
By typing findstr /?
you will reveal the large number of options available for pre or post SQL processing.