SQL Script consolidator

Question

SQL Script consolidator

khushbu

SSCarpal Tunnel

Points: 4591
More actions
October 13, 2008 at 1:04 am

#126042

Hi
I need to build many databases in a day.
We usually follow the process of executing the script kept at one location(typically VSS).
In that folder eack sp,function etc is kept as separate file, and opening executing and then closing file many times is time consuming (and boring too).
Does somebody has tool which will if given the path of scripts kept, will consolidate i.e make one file out of 10 files.
This would make my task easier and there would be less chance of me missing any script

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

This code is a VB Script file that will read a folder and add the text of each file to a new (combined) file. The combined file can be executed from SSMS or from the command prompt with SQLCMD.

Copy this code to a new file with extention .vbs. Adjust the mentioned location and filename to your

needs. Execute this from the command prompt and open the CombinedFile to check if it's created correct.

Const ForReading = 1

Const ForWriting = 2

Const ForAppending = 8

sFolderSource = "C:\Temp"

sCombinedFile = "C:\CombinedFile.sql"

Set objShell = CreateObject("Shell.Application")

Set objFSO = CreateObject("Scripting.FileSystemObject")

' ==================================

' == Check if Folder is available ==

If Not objFSO.FolderExists(sFolderSource) Then

WScript.Quit

End If

' =================================================

' == Locate and read all files from sourcefolder ==

Set objFolderS = objShell.Namespace(sFolderSource)

Set objTextFile = objFSO.OpenTextFile (sCombinedFile, ForAppending, True)

Set colItems = objFolderS.Items

For Each objItem in colItems

if objItem.type <> "Folder" then

' this script assumes all files in the folder are readable .txt/.sql files

objTextFile.WriteLine("")

' add a kind of title before adding the lines to locate the file in the combined file

objTextFile.WriteLine("/********************************************")

objTextFile.WriteLine("***** " & objItem.Name)

objTextFile.WriteLine("********************************************/")

' add a BEGIN TRAN statement to execute the code of each file seperate

objTextFile.WriteLine("BEGIN TRAN")

Set objFile = objFSO.OpenTextFile(sFolderSource & "\" & objItem.Name, ForReading )

Do Until objFile.AtEndOfStream

objTextFile.WriteLine(objFile.ReadLine)

Loop

objTextFile.WriteLine("")

' add a COMMIT TRAN statement to execute the code of each file seperate

objTextFile.WriteLine("COMMIT TRAN")

objTextFile.WriteLine("/********************************************/")

objFile.Close

end if

Next

objTextFile.close

msgbox "Combined file created."

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 2

I'd use a batch file, and put the file names in there. You need to control ordering at times, so I'd be wary of a script like the one above.

http://www.sqlservercentral.com/articles/System+Development+Life+Cycle/vcspart3/525/

brainy SSCrazy Points: 2445 More actions · Answer 3

Its better user batch files. Normally we run scripts using batch files.

There is one utility called osql (older one for sql 2000 & 2005),

now isql (from 2005 onwards) used to execute the script stored in a file. I recommend u to use that utility which comes with SQL Server.

You need to create a batch file, inside batch u need to use the isql or osql utility to call the script file, in the sequence which u want to execute.

For more info on osql/isql search in microsoft books online.

noeld SSC Guru Points: 96590 More actions · Answer 4

noeld

SSC Guru

Points: 96590

October 14, 2008 at 2:52 pm

#884913

sqlcmd can be your friend!

* Noel