?
Solved

How to bring ALL StoredProcedures from one DB to another DB

Posted on 2013-06-08
11
Medium Priority
?
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1600 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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 400 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

801 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