Just A Programmer We're just programmers

26Mar/110

How many ways can we ‘script’ a stored proc or UDF from the command line?

Update: Added link to the scripts on github.

If you’ve worked with SQL Server for any length of time, you’ve probably written some stored procedures and user defined functions. If you’re like me, you’ve written many. Usually, when you edit a stored proc or UDF. you do it from Visual Studio or Sql Server Management Studio (SSMS). However, what if you want to script the sproc or udf from the command line? Well, there are quite a few ways to skin this cat, and I’ll go through a few.

Using sqlcmd

The simplest way to do this is the command line client that ships with SQL server 2005 and up, sqlcmd, and a system stored proc called sp_helptext. For our example lets have sp_helptext script itself.

sqlcmd -S .sqlexpress2k8r2  -d master -h-1 -s"" -W -w 1024 -Q "EXEC sp_helptext 'sp_help'" -o sp_helptext.sql

Lets breakdown the arguments:

  • -S .sqlexpress2k8 this is the named instance of sql server I am connecting to. If you want to connect to the local default instance, you can omit this
  • -d master This is the database we want to connect to. This can be omitted if you want to script an object from the users default catalog.
  • -h-1 -s”” -W -w 1024 These format the output of sqlcmd in a way that makes sense for this task. If you want to understand these options, look at the sqlcmd msdn page, or this stackoverflow question.
  • -Q “EXEC sp_helptext ‘sp_helptext’” -Q means “execute this T-SQL and exit.
  • -o sp_helptext.sql This writes the command output to the text file sp_helptext.sql

Using a batch file

Now we could easily wrap this in a batch file as I demonstrate here:

C:UserszippyDocumentsdeleteme>type scriptProcOrUdf.cmd
@echo off
sqlcmd -S %1  -d %2 -h-1 -s"" -W -w 1024 -Q "EXEC sp_helptext '%3'" -o "%3.sql"<span style="font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 13px; line-height: 19px; white-space: normal;">
</span>
So we saved ourselves a few keystrokes with a 2 line batch file.

Using Powershell

Batch files work, but they’re so 2002. Powershell is the CLI of choice these days on windows. So lets see how we would do this in powershell:

So apparently it took me 26 lines of Powershell (if you remove the BSD License) to accomplish what I could have done in a 2 line batch file. Whats the point you might be asking? Well, honestly if you just want the quickest way to script stored procedures to a file from the command line, I’d use the batch file. However, if you wanted to make changes to this script, for example, make it script all stored procedures and udfs in the database. You’d have an easier job with Powershell. You could turn the body of the script into a Poweshell function that takes a SqlConnection object and object name as a parameter. You could then get a list of all the stored procedures and functions with the T-SQL statement SELECT [ROUTINE_NAME], [ROUTINE_TYPE], [ROUTINE_DEFINITION] FROM [INFORMATION_SCHEMA].[ROUTINES]. I’ll leave writing that script as an exercise to the reader.

Using Powershell and “Original Thinking” to do it in less than 26 LOC

In the fictional Star Trek universe, Denny Crane, James T. Kirk cheated on the Kobayashi Maru by reprogramming the no-win scenario to allow him to win. He was awarded for his original thinking. Our scenario is less dire. However, like Kirk, we can change the rules. I chose to use a the open source .NET assemble Atlantis.SchemaEngine.dll. This dll is used for synchronizing schema between two SQL server databases. We don’t want to synchronize or compare anything. However, the library synchronizes database schema’s by generating SQL scripts. Therefore, it can generate DDL for any script-able object in a database. So lets take a look at our leaner, meaner script.

So we went from 26 to 15 lines. That’s a savings of 42%. Some of those are pretty long lines since we have to fully qualify our namespaces in Powershell. However, its a net simplification compared to making the ADO.NET calls directly.

Conclusion

I showed you 4 ways to script a stored proc. I also left plenty of room for you the reader to explore on your own.  The code for ScriptProc.ps1 and Script-Object.ps1 lives in the justaprogrammer powershell github repo. If you have any improvements, feel free to leave a comment or pingback below. Also, pull requests are always welcome. Happy Scripting!