Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to bring ALL StoredProcedures from one DB to another DB

Posted on 2013-06-08
11
Medium Priority
?
262 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Loops Section Overview

610 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