Solved

How to bring ALL StoredProcedures from one DB to another DB

Posted on 2013-06-08
11
257 Views
Last Modified: 2016-02-11
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.
0
Comment
Question by:Easwaran Paramasivam
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39231664
You can use the import wizard from the destination to import the stored procedures as any objects.

Hope this helps.
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39231928
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 39232202
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Expert Comment

by:karthi7688
ID: 39233906
In-built export/import wizard in SSMS can help export/import any type of database objects.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 39237359
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
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39244980
@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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39245104
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
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39253613
@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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39254093
I am sorry, I guess I don't understand your question.

Good luck.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 100 total points
ID: 39254117
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
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39255259
Thanks.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question