Search and Replace .sql files from Script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Search and Replace .sql files from Script

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-29 : 15:20:14

I am not sure if anyone needs this but when you script out all your sql objects the file generated has the object type tacked on to it at the end and you might not like that. Here is a vbscript that cleans up those files in a folder.


Dim fso, objFolder, objFile, colFiles, strNewName

Const WINDOW_HANDLE = 0
Const NO_OPTIONS = 0

Set fso = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder(WINDOW_HANDLE, "Select a folder:", NO_OPTIONS, "C:\Scripts")
Set objFolderItem = objFolder.Self
objPath = objFolderItem.Path
objPath = Replace(objPath, "\", "\\")

Set objFolder = fso.GetFolder(objPath)
Set colFiles = objFolder.Files

For Each objFile In colFiles
   If fso.GetExtensionName(objFile) = "sql" Then
     If InStr(objFile.Name, "UserDefinedFunction") > 0 Then
         strNewName = Replace(objFile.Name, ".UserDefinedFunction", "")
     ElseIf InStr(objFile.Name, "Schema") > 0 Then
         strNewName = Replace(objFile.Name, ".Schema", "")
     ElseIf InStr(objFile.Name, "StoredProcedure") > 0 Then
         strNewName = Replace(objFile.Name, ".StoredProcedure", "")
     ElseIf InStr(objFile.Name, "Table") > 0 Then
         strNewName = Replace(objFile.Name, ".Table", "")
     ElseIf InStr(objFile.Name, "View") > 0 Then
         strNewName = Replace(objFile.Name, ".View", "")
     ElseIf InStr(objFile.Name, "User") > 0 Then
         strNewName = Replace(objFile.Name, ".User", "")
     ElseIf InStr(objFile.Name, "Role") > 0 Then
         strNewName = Replace(objFile.Name, ".Role", "")
     ElseIf InStr(objFile.Name, "SqlAssembly") > 0 Then
         strNewName = Replace(objFile.Name, ".SqlAssembly", "")
     Else
         strNewName = objFile.Name
     End If
      fso.MoveFile objFile.Name, strNewName
   End If
Next

you will need to customize it to your own need. there probably is something better than this.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources