How to copy table with indexes and store procedures?

Question

How to copy table with indexes and store procedures?

OGINTZ Marina 175

Hi, I have pipeline that use lookup to get all the tables name and then I preform a copy of all the tables from source to target. I noticed that it doesn't copy indexes and store procedure. Can you please assist me how to do it?

1 answer

Your answer

Answer 1

Azar 29,520 MVP Volunteer Moderator

Hey OGINTZ Marina

I think you need to separately manage the copying of indexes and stored procedures, as they are not automatically included in a typical table copy operation.

lemme help you on how to do this.

Use your preferred method to copy tables from the source to the target. Tyou can do this through tools like Azure Data Factory, SSIS.

Identify and script out the indexes from the source database. You can use SSMS to generate scripts for each index.

In SSMS, you can script indexes by right-clicking on the table, selecting "Script Table as," and choosing "CREATE To" or "DROP and CREATE To" for scripting the index. Run the generated scripts on the target database.

Similarly, script out the stored procedures from the source database using SSMS, Right-click on the stored procedure, select "Script Stored Procedure as," and choose "CREATE To" or "DROP and CREATE To" for scripting the stored procedure.

Finally run the stored procedure scripts on the target database. If this helps kindly accept the answer thanks much.

OGINTZ Marina 175 Reputation points

2024-01-17T18:26:37.67+00:00

Thanks for your answer. I need to do it only with Data factory
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-01-23T23:54:36.79+00:00

Hello OGINTZ Marina,

one way to solve this is by using a "Stored Procedure" Activity to execute a stored procedure that creates the indexes and stored procedures in the target database.

Ex:

CREATE PROCEDURE CreateIndexesAndStoredProcedures AS BEGIN -- Create indexes CREATE INDEX idx_table1_col1 ON table1 (col1); CREATE INDEX idx_table1_col2 ON table1 (col2);

-- Create stored procedures CREATE PROCEDURE sp_procedure1 AS BEGIN -- Procedure code END;

CREATE PROCEDURE sp_procedure2 AS BEGIN -- Procedure code END;

END;
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-01-29T18:05:00.15+00:00

Hello OGINTZ Marina, I am checking to see if you had a chance to look into my above response.

Share via

How to copy table with indexes and store procedures?

1 answer

Your answer