Feb 7, 2013

Sql server export stored procedures


There are many different ways to export or imports the stored procedures from one database to another database, may be are in different sql servers:

By generating the script of stored procedure using sql server management studio (SSMS)

Step 1: Right click on the source database of stored procedures and choose Tasks - > Generate Scripts



Step 2: In chose objects you can chose stored procedures according to our requirement.


Step 3: In Set Scripting Options window set the location where you want to save the generated script. In this example we are saving in new query window. Click on Next button.


Step 4: Again click on Next button in new window. And then click on finish button after generating the script of selected stored procedure
Step 5: Copy the generated script of stored procedure and execute the in the database where you want to export.
Note: At the top of script you may find the script like:

USE [AdventureWorks2008R2]
GO

You have to replace the database name that is [AdventureWorks2008R2] by your destination database name.

By getting the script of stored procedures by sql query:

Step 1: Execute the following sql in the source database. Modify WHERE clause according to your requirements:

SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
     AND ROUTINE_NAME IN ('uspProc1','uspProc2')

No comments:

Post a Comment