Recursive Directory Scripting

Inside source control, I have scripts arranged as follows:

Drive:/Base/Database/
Drive:/Base/Database/Functions
Drive:/Base/Database/Proc
Drive:/Base/Database/Tables
Etc..

Each script stored can be run multiple times with no ill effect. For example, a table generation script first checks to see that it exists before running a create.

I was troubleshooting an issue where it seemed the source and the database were out of sync, so I needed to push everything I had to a test database to continue my troubleshooting, and I didn’t want to run each script by hand.

I knew about sqlcmd, and wanted to quickly script out each file to run on the target database. I also needed a list of all the files in each of those layers of directories.

In the windows environment, it was surprisingly difficult to accomplish a recursive search for files to run, so I had to do some Google sleuthing to figure it out.

There were too many sources to link them all properly, but this was patched together from all over the web. If there’s a better method, I’d love to hear about it.

Here are the steps I followed:

– Open up a command prompt window
– Navigate to the root directory where my scripts are stored (in this example: Drive:/Base/Database/)
– run: for /r %d in (*.sql) do echo %d > sqlcmd_files.bat
(alternatively change > to >> to append to sqlcmd_files.bat instead of overwriting it)
– Edit sqlcmd_files.bat w/ your favorite text editor
– Find and Replace the Drive:\ with sqlcmd -Sserver_name -ddatabase_name -iDrive:\
(this should change your lines from Drive:/Base/Database/Proc/Filename.sql to sqlcmd -Sserver_name -ddatabase_name -iDrive:\Base/Database/Proc/Filename.sql )
– Save and execute.

Known limitations:
– Doesn’t take into account dependencies. If the tables happen to script out after a stored proc that uses that table sqlcmd will throw an error. In my case, I just ran the sqlcmd_files.bat twice. The first time laid down anything without dependencies, and the second time cleaned everything up.
– Specifically looks for files with the extension “.sql”. If you use something else, adjust accordingly.
– All of my paths have no spaces in them. If you do, consider wrapping in quotes around your paths.

This entry was posted in SQL Server Development, SQL Toolbox and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *