Coping stored procedure

shailu · December 30th, 2004, 03:33 AM

Hello to all experts out there

How do I copy stored proceudres and table structures from one database to another using SQL. :)

Not need to use DTS

madhukp · December 30th, 2004, 06:51 AM

You can generate SQL of your entire database through Enterprise Manager.

1) Open Enterprise Mgr.

2) Locate database

3) Right click. Open All Tasks -> generate SQL script

4) In the window, select the objects you wish to include in the SQL

5) generate

6) Then through query analyzer, select the new database.

7) Open the abopve SQL with the new database and run.

shailu · December 30th, 2004, 08:30 AM

hi madhukp
Actually I have done the same thing sucessfully but just wondering any other way.
also giving a thought on coping only the structure of tables from one db to another.

jemacc · December 30th, 2004, 11:19 AM

I used this from a fellow co-worker see if it will be of any use to you
If the table name(s) is(are) going to be variable, you will need to use dynamic SQL. For example:

Code:

CREATE PROCEDURE whatever
    @newTable VARCHAR(40),
    @oldTable VARCHAR(40)
AS
EXEC('SELECT * INTO ' + @newTable + ' FROM ' + @oldTable + ' WHERE 1 = 0')
EXEC('ALTER TABLE ' + @newTable ' ADD newColumn newDataType, ...')

Jaime E. Maccou

JayBow77 · June 30th, 2006, 02:52 PM

I create a sql server dts job, and now I want to copy it to a diferent server registration how do you do this.