Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

up vote 3 down vote accepted

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

enter image description here

enter image description here

enter image description here

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)

share|improve this answer
add comment

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"
}
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.