Solved

How to bring ALL StoredProcedures from one DB to another DB

Posted on 2013-06-08
11
259 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 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 65
SQL - Curser to do an insert based on a select 2 29
MS SQL Sever Import/export problem 7 69
T-SQL: Need Group By to use "fuzzy logic"?? 3 51
In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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