How to bring ALL StoredProcedures from one DB to another DB

I'd like to bring ALL StoredProcedures from new version DB to older version DB. I would like to drop the existing SPs in older version before that.

How to achieve it?

Please do assist.
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Something like this will script them out using Results to Text:
USE NewDatabase

SELECT OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE name in ('usp_StoredProcedure1', 'usp_StoredProcedure2', 'usp_StoredProcedure3', ...)

You can then run these scripts on the old database.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
You can use the import wizard from the destination to import the stored procedures as any objects.

Hope this helps.
0
 
Easwaran ParamasivamAuthor Commented:
I'm working on automation tool. Hence it would be great if you share your idea how to achieve this without the build-in tools.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
karthi7688Commented:
In-built export/import wizard in SSMS can help export/import any type of database objects.
0
 
Alpesh PatelAssistant ConsultantCommented:
Best way to use SSIS package and in that use Transfer SQL Object task and in that select all Stored procedures.

It will transfer all SP to other DB.

Transfer Object
0
 
Easwaran ParamasivamAuthor Commented:
@acperkins: I don't want to pass hardcoded SP names as parameter as I do know the SP names at runtime.

@PatelAlpesh: Intresting. Could you please share more details how to achieve it? Please throw some more light on it. Please do provide an example on it or suggest some videos or article to go through. So that I could learn and do as I'm new to SSIS.
0
 
Anthony PerkinsCommented:
I don't want to pass hardcoded SP names as parameter as I do know the SP names at runtime.
That does not make sense.  But if you are trying to say that you do not know the names then lose the WHERE clause as in:
SELECT OBJECT_DEFINITION(object_id)
FROM sys.procedures
0
 
Easwaran ParamasivamAuthor Commented:
@acperkins: Thanks. But the scripts contains 'CREATE PROC..' But  I would like to have IF EXIST.. DROP.. CREATE Sps.

I would like to achieve same thing for UDFs as well. Please do suggest.
0
 
Anthony PerkinsCommented:
I am sorry, I guess I don't understand your question.

Good luck.
0
 
Racim BOUDJAKDJIConnect With a Mentor Database Architect - Dba - Data ScientistCommented:
something like this should help...please assign points to acperkins

SELECT 'if object_id(''' + name + ''') is not null drop procedure '+name+';'+
OBJECT_DEFINITION(object_id)
FROM sys.procedures

Open in new window

0
 
Easwaran ParamasivamAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.