Existing Stored Procedures

hi,

is there someway I can use to copy an existing stored procedures to another database?

Thanks!

jocker_wow:

copy an existing stored procedures to another database?

This is one way:
Goto Enterprise Manager then select Server, DB and Select the stored procedure
Tip: To select more that one proc do Shift and select.
After selecting stored proc or procs right click and

select "All Task" and then "Generate SQL Script".
Then you see a screen Pop-Up - then select "Options" in that Tab.
Please Check- Script Object-Level Permissions - for permissions.
Then say ok and give the path where you want to store the file.

Next step is open the file created by SQL server in Query analyser and execute it on the destination Database.

Another way of doing this:

Sp_helptext "Stored Proc name" for the list of stored you need move.
Note: Only one proc name at a time. eg:
Sp_helptext "Proc1"
Sp_helptext "Proc2" etc

Tips: change the display mode of the query analyser to Text mode
Then copy all the result to another window and execute it against desitnation DB.
Note: In this way it will not copy permissions.

another helpful link..

Need help with copy stored procedure from one database to another - MSDN Forums

hope it helps./.

|||

Thankskaushalparik27! I've already found that solution, but my problem still exists!

Coping the stored procedures was a part from my big problem, I want to copy my local database to a remote database, you can check my posthere if you can help

|||

Hi

copy means, i can'tunderstandexactly what you need? anyway we can use a store procedure to another database if the same table and fields are there what you mentioned in existingstored procedure by create procedure on new database

|||

Yes it is the same database but it exists in the remote host. I've solved the SP part, but I'm stuck in other problem:

http://forums.asp.net/p/1193730/2057106.aspx#2057106

Thanks in advance

|||

if you generate Script for your table from "All Task" then it will also script the PK, FK relation ships between tables...

here is one sample script generated...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_1]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Table_1](
[id] [nchar](10) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_2]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Table_2](
[id] [nchar](10) NULL
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Table_2_Table_1]') AND parent_object_id = OBJECT_ID(N'[dbo].[Table_2]'))
ALTER TABLE [dbo].[Table_2] WITH CHECK ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([id])
REFERENCES [dbo].[Table_1] ([id])
GO
ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]