Link to home
Start Free TrialLog in
Avatar of Easwaran Paramasivam
Easwaran ParamasivamFlag for India

asked on

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.
Avatar of Racim BOUDJAKDJI
Racim BOUDJAKDJI
Flag of Algeria image

You can use the import wizard from the destination to import the stored procedures as any objects.

Hope this helps.
Avatar of Easwaran Paramasivam

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of karthi7688
karthi7688

In-built export/import wizard in SSMS can help export/import any type of database objects.
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.

User generated image
@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.
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
@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.
I am sorry, I guess I don't understand your question.

Good luck.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks.