What have you tried so far? You should really do some research into the possible solutions and try a few things.
I also think this question may be better suited to StackOverflow.
Anyway, I've modified a VBScript
that I wrote a while ago that achieves what you want. I'm very much a VBScript novice, so please forgive the long-windedness of it. I'm sure there's a more elegant solution, but it does definitely work. The script assumes that the files will be encountered in order so you might run into some issues.
I have made the script available through my Google Drive
account:
https://docs.google.com/file/d/0B-7FwCWf15oGd2R0R1RMaHdmRFk/edit?usp=sharing
You'll only have to change the file path in order for the script to work. The VBScript
code in case the file doesn't work:
Dim sName
Dim fso
Dim folder
Dim count
count = 1
Set fso = WScript.CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("Your folder path")
For Each file In folder.Files
If count < 10 Then
file.Name = "myname-script.00" + CStr(count) + ".sql"
End If
If count > 9 And count < 100 Then
file.Name = "myname-script.0" + CStr(count) + ".sql"
End If
If count > 99 And count < 10000 Then
file.Name = "myname-script." + CStr(count) + ".sql"
End If
count = count + 1
Next