How can you save all user-defined functions to txt files on your local hard drive?

Question

I am running SQL Server 2008 and need to back up the UDF scripts/queries that I have created. How can this be done in a timely manner? I tried right clicking on the folder icon where the UDF are saved (Table-Valued Functions) to copy the folder but am not bale to.

The only thing I can think up is using the MODIFY command on each and every single one my UDF scrips and copying and pasting to separate text files.

Even If if there is only a simple way to save ALL the Scripts in one text file instead of separate txt files I would be more than happy to do that instead of having to copy and paste every single file one at a time.

Any suggestions?

Answer 1

SSMS (via SMO) allows you to script it at the category (function) level. It won't allow you to specify only table valued functions.

You could also look at something like this article for using PS to automate the creation. You might be able to filter the objects being scripted by the function type (tvf, scalar, etc)

Answer 2

The easiest way I can think of is the following:

1. Right-click on your database -> Tasks -> Generate Scripts...
2. Select specific database objects
3. Select the checkbox User-Defined Functions
4. Save to file and just name your text file whatever you'd like

This will generate and save the scripts for your UDFs to your desired file. You could use PowerShell (off the top of my head) to generate scripts for each UDF and save them to a separate text file (one text file per UDF).

Edit: Below is some PowerShell to do what you want to do. This script will get all of the User Defined Functions in your database (set the variables to your appropriate environment) and write the definition of each of them to their own file, naming the file what the function is named.

$serverName = "YourServerName"          # example: "YourServer" or "YourServer\YourNamedInstance"
$databaseName = "YourDatabaseName"
$outputDirectory = "C:\Dir1\YourUDFs"   # the dir you want these files saved in

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName)
$database = $server.Databases.Item($databaseName)
$udfCol = $database.UserDefinedFunctions | Where-Object {$_.Schema -ne "sys"}
foreach ($udf in $udfCol) {
    $udfName = $udf.Name
    ($udf.TextHeader + $udf.TextBody) | 
        Out-File -FilePath $outputDirectory\$udfName".txt"
}

Answer 3

Method 1

In Object Explorer, expand your [server] => [database] => Programmability => Functions and highlight the appropriate [function type] (e.g. Scalar-valued Functions).

Now open Object Explorer Details (F7).

In the right-hand pane, select all the functions, then right-click, Script Function As => CREATE To => File.

I don't think you can use this method for all function types at once.

---

Method 2

This is a little more manual, but you can put the metadata into your own script and treat all functions the same using a query like this (make sure you use results to text):

SET NOCOUNT ON;

SELECT m.definition + 'GO'
  FROM sys.sql_modules AS m
  INNER JOIN sys.objects AS o
  ON m.[object_id] = o.[object_id]
  WHERE o.type IN ('FN', 'IF', 'TF');

If you have a lot of functions, this will work better if you consume it from an application that doesn't have the same display limitations as SSMS.

---

Method 3

Look into SMO, PowerShell, etc.