Copy Scalar Functions from one Database to another

Question

How do I copy just the Scalar Functions from one Database to another? I'm not worried about copying any tables or data. I tried performing an Export Task but that seemed to only let me move tables.

You should take a look to: http://stackoverflow.com/questions/422525/tools-to-generate-sql-ddl-and-content-from-an-existing-database — dani herrera, Feb 03 '12 at 20:49

score 1 · Accepted Answer · edited May 23 '17 at 10:32

These steps were done on SQL Server 2008 R2 in SSMS.

In short, I used Task -> Generate Scripts... instead of Script Database as -> Create To. The latter only returned a SQL script to create the Database (e.g. Create Database, Alter Database, and Filegroups) without creating any other objects in the Database (e.g. Tables, Views or Functions).

Here are the exact steps with screenshots:

Right click on the database with the functions you want and go to Generate Scripts
Click through the first screen of the Wizard
Choose User-Defined Functions
Finish the wizard.

Also, this answer, while it isn't an exact corollary, prompted me to look for the Generate Scripts option.

score 0 · Answer 2 · answered Feb 04 '22 at 13:04

-- This program copies (CREATE OR ALTER THE FUNCTION) a single Function from one database to another
-- *** Note that all objects mentioned in the function must exist in the target database ***
declare @SourceDatabase nvarchar(50);
declare @SourceSchemaName nvarchar(50)
declare @TargetDatabase nvarchar(50);
declare @FunctionName nvarchar(50);


set @SourceDatabase =   N'Northwind'        -- The name of the Source database
set @SourceSchemaName = N'dbo'              -- The name of the Function SCHEME
set @FunctionName =     N'WriteToTextFile'  -- The name of the Function   
set @TargetDatabase =   N'AdventureWorks'   -- The name of the Target database
  
declare @sql nvarchar(max) 
 
-- If the Function SCHEME does not exist, create it
set @sql = ' use [' +@TargetDatabase +'] ' +
            ' IF NOT EXISTS (SELECT * FROM sys.schemas WHERE lower(name) = lower(''' + @SourceSchemaName + ''')) '+
            ' BEGIN ' +
            '   EXEC('' CREATE SCHEMA '+ @SourceSchemaName +''') ' +
            ' END'
 
exec (@sql);

-- CREATE OR ALTER THE FUNCTION
set @sql = ''
set @sql = @sql + ' use [' + @TargetDatabase +'] ;' +
                    ' declare @sql2 nvarchar(max) ;' + 
                    ' SELECT @sql2 = coalesce(@sql2,'';'' ) + [ROUTINE_DEFINITION] + '' ; '' ' +
                    ' FROM  ['+@sourceDatabase+'].[INFORMATION_SCHEMA].[ROUTINES] '  +
                    ' where ROUTINE_TYPE = ''FUNCTION'' and ROUTINE_SCHEMA = ''' +@SourceSchemaName +''' and lower(ROUTINE_NAME) = lower(N''' + @FunctionName + ''')  ; ' +
                    ' set @sql2 = replace(@sql2,''CREATE FUNCTION'',''CREATE OR ALTER FUNCTION'')' +
                    ' exec (@sql2)'
exec (@sql)

Copy Scalar Functions from one Database to another

2 Answers2