I come across the need occasionally to deploy a set of sql files that are all checked into source control in different files with a file hierarchy like this:
- Database Name
- Type of object (proc, table, view, etc)
- Name of object
- Type of object (proc, table, view, etc)
When I go to deploy the scripts I need to manually combine all the SQL files into one to move to production, qa or test for deployment. After getting annoyed at lots of copy and paste I finally discovered an easy powershell script to combine all the files into one.
First start by moving to the top directory containing all the sub directories that you want to deploy. The following script assumes that all files ending in .sql should be merged.
Get-ChildItem -Path . -Filter *.sql -Recurse -ErrorAction SilentlyContinue -Force | gc | out-file C:\YourPathGoesHere\DeployScript.sql
This makes it very quick to combine a batch of like files for deployment.
Possible issues to be aware of:
- If a script contains a stored proc, or other object that doesn’t end in a GO, then it gets merged with the next one. I have dealt with this by manually reviewing the files.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!