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
   

- Advertisement -