How to delete 3 lines of code in a sql script

I have a few hundred sql scripts where I need to delete 3 lines of codes and delete the very last line of code.  The first 3 are:
DECLARE @dtStartCol datetime
DECLARE @dtEndCol      datetime
DECLARE @dtLastUp      datetime.  The last line is garbage that was place in by accident.
All of these files are in 1 folder and ends with .sql.
wdbatesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jini JoseSenior .Net DeveloperCommented:
if you are familiar with any language, then use to read the files using any type of file system object. then read the content line by line then check for the line you want to delete. then remove that line and save the file.
0
wdbatesAuthor Commented:
I have relocated off the mainframe, DB2 to SQL2005.  On the mf I would use REXX along with Xedit and loop through the files looking for the string or last line and delete that record or row.  I am sure there is something in DOS or XP that could do the same.
0
Patrick MatthewsCommented:
You could try this VBScript.  It doesn't do anything about the last line yet.  Can you state some sort of rule about what it contains?




Dim fso, fld, fil, ts1, ts2, line, regx

Const BaseFolder = "c:\SQL Scripts"
Const PatternStr = "DECLARE +@dt(StartCol|EndCol|LastUp) + datetime *"

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(BaseFolder) 'update
fso.CreateFolder BaseFolder & "\Clean"

Set regx = New RegExp
With regx
    .IgnoreCase = True
    .Pattern = PatternStr
End With

For Each fil In fld.Files
    If UCase(Right(fil.Name, 4)) = ".SQL" Then
        Set ts1 = fso.OpenTextFile(fil.Path)
        Set ts2 = fso.CreateTextFile(BaseFolder & "\Clean\" & fil.Name)
        Do Until ts1.AtEndOfStream
            line = ts1.ReadLine
            If Not regx.Test(line) Then
                ts2.WriteLine line
            End If
        Loop
        ts1.Close
        ts2.Close
    End If
Next

Set regx = Nothing
Set ts1 = Nothing
Set ts2 = Nothing
Set fil = Nothing
Set fld = Nothing
Set fso = Nothing

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Patrick MatthewsCommented:
Change:

Const PatternStr = "DECLARE +@dt(StartCol|EndCol|LastUp) + datetime *"

to:

Const PatternStr = "DECLARE +@dt(StartCol|EndCol|LastUp) +datetime *"
0
wdbatesAuthor Commented:
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿°¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿(¿¿¿¿4¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿°¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿(¿¿¿¿4¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿°¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿(¿¿¿¿4¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿°¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿(¿¿¿¿4¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿°¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿(¿¿¿¿4¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿°¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿(¿¿¿¿4¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿°¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿(¿¿¿¿4¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿°¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿(¿¿¿¿4¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿°¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿(¿¿¿¿4¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿°¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿(¿¿¿¿4¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿°¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿(¿¿¿¿4¿¿¿4¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿°¿¿¿¿¿¿¿¿¿¿¿¿¿¿
0
Patrick MatthewsCommented:
?
0
wdbatesAuthor Commented:
In Rexx or Xedit I could go to the end and delete.  Is there something like that in DOS Xp?

When I create this VB Script what is the file type and in LINE 3, should I replace that with the correct file path?
0
Patrick MatthewsCommented:
This may dela with the last line as well:



Dim fso, fld, fil, ts1, ts2, line, regx, arr, i

Const BaseFolder = "c:\SQL Scripts"
Const PatternStr = "DECLARE +@dt(StartCol|EndCol|LastUp) + datetime *"

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(BaseFolder) 'update
fso.CreateFolder BaseFolder & "\Clean"

Set regx = New RegExp
With regx
    .IgnoreCase = True
    .Pattern = PatternStr
End With

For Each fil In fld.Files
    If UCase(Right(fil.Name, 4)) = ".SQL" Then
        Set ts1 = fso.OpenTextFile(fil.Path)
        arr = Split(ts1.ReadAll, vbCrLf)
        Set ts2 = fso.CreateTextFile(BaseFolder & "\Clean\" & fil.Name)
        For i = 0 To UBound(arr) - 1
            line = arr(i)
            If Not regx.Test(line) Then
                ts2.WriteLine line
            End If
        Next
        ts1.Close
        ts2.Close
    End If
Next

Set regx = Nothing
Set ts1 = Nothing
Set ts2 = Nothing
Set fil = Nothing
Set fld = Nothing
Set fso = Nothing

Open in new window

0
wdbatesAuthor Commented:
I figured out my last 2 questions about filetype and execution.  I saved the vbs in the same folder with the files and changed line 3 from Const BaseFolder = "c:\SQL Scripts" to Const BaseFolder = "c:\ Data Scripts"

I open a DOS session and received a VBSCript runtime error: Path not found.

0
wdbatesAuthor Commented:
The full path name is C:\Documents and Settings\sql_team\My Documents\SQL Server Management Studio\Projects\data
0
Patrick MatthewsCommented:
Then use that full path in the constant declaration.  Case does not matter, but spelling does.
0
wdbatesAuthor Commented:
What about the word Scripts after the path?  With it in I receive a Path not found, when I take it out  I receive a Invalid procedure call or argument.  It creates the Clean folder and there is 1 file there, but empty.
0
Ephraim WangoyaCommented:

You can use an application like Notepad++ to do this sort of thing
http://notepad-plus-plus.org/
0
wdbatesAuthor Commented:
The command I use to execute is cscript /nologo delete3.vbs
0
Patrick MatthewsCommented:
My previous suggestion neglected to include the fix in the pattern string.  

I tested it successfully on my end.

Be sure to update BaseFolder before running.


Dim fso, fld, fil, ts1, ts2, line, regx, arr, i

Const BaseFolder = "c:\SQL Scripts" 'update as needed
Const PatternStr = "DECLARE +@dt(StartCol|EndCol|LastUp) +datetime *"

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(BaseFolder) 'update
fso.CreateFolder BaseFolder & "\Clean"

Set regx = New RegExp
With regx
    .IgnoreCase = True
    .Pattern = PatternStr
End With

For Each fil In fld.Files
    If UCase(Right(fil.Name, 4)) = ".SQL" Then
        Set ts1 = fso.OpenTextFile(fil.Path)
        arr = Split(ts1.ReadAll, vbCrLf)
        Set ts2 = fso.CreateTextFile(BaseFolder & "\Clean\" & fil.Name)
        For i = 0 To UBound(arr) - 1
            line = arr(i)
            If Not regx.Test(line) Then
                ts2.WriteLine line
            End If
        Next
        ts1.Close
        ts2.Close
    End If
Next

Set regx = Nothing
Set ts1 = Nothing
Set ts2 = Nothing
Set fil = Nothing
Set fld = Nothing
Set fso = Nothing

Open in new window

0
wdbatesAuthor Commented:
Invalid procedure call or argument, Line 25 char 17
0
Patrick MatthewsCommented:
Please post the code exactly as you tried to implement it when you got that error.
0
Anthony PerkinsCommented:
ewangoya,

>>You can use an application like Notepad++ to do this sort of thing<<
On hundreds of files?  Are you volunteering?  :)
0
wdbatesAuthor Commented:
Dim fso, fld, fil, ts1, ts2, line, regx, arr, i

Const BaseFolder = "C:\Documents and Settings\sql_team\My Documents\SQL Server Management Studio\Projects\data"

Const PatternStr = "DECLARE +@dt(StartCol|EndCol|LastUp) +datetime *"

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(BaseFolder) 'update
fso.CreateFolder BaseFolder & "\Clean"

Set regx = New RegExp
With regx
    .IgnoreCase = True
    .Pattern = PatternStr
End With

For Each fil In fld.Files
    If UCase(Right(fil.Name, 4)) = ".SQL" Then
        Set ts1 = fso.OpenTextFile(fil.Path)
        arr = Split(ts1.ReadAll, vbCrLf)
        Set ts2 = fso.CreateTextFile(BaseFolder & "\Clean\" & fil.Name)
        For i = 0 To UBound(arr) - 1
            line = arr(i)
            If Not regx.Test(line) Then
                ts2.WriteLine line
            End If
        Next
        ts1.Close
        ts2.Close
    End If
Next

Set regx = Nothing
Set ts1 = Nothing
Set ts2 = Nothing
Set fil = Nothing
Set fld = Nothing
Set fso = Nothing
0
Patrick MatthewsCommented:
I just ran that same code successfully.  The only change I had to make was the specified directory for BaseFolder.

Can you upload some of the files here?  You may have to change the extensions to .txt; I cannot remember if .sql is on EE's whitelist or not.
0
wdbatesAuthor Commented:
Here is a file to use
LtbHomeCondition.sql
0
wdbatesAuthor Commented:
I used SQL Server Management Studio and open all the queries at one time.  At that time I performed a Find and Replace and selected All open documents in Look In:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>I accepted my solution as it was the only one that worked.<<
I suspect you mean:
I accepted my solution as it was the only one that I could get to work.
0
wdbatesAuthor Commented:
I accepted my solution as it was the only one that worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.