CODEWITHSUNDEEP
c#sql-serverssis
Sql Dev
Sql Dev

Reputation: 11

Import 10,000+ stored procedures

I need to import 10,000+ stored procedures from a clients environment into my company's environment.

One option is to create a C# based application which creates these 10K+ stored procedures.

I have knowledge of DataSet, DataReader etc. but I do not know how to implement this.

Another option - I have created an SSIS package using a ForEachLoop, taking files from folder and loads/executes in DB using Execute SQL Task. This takes too much time (2000 files = 1 hour)

So can you guys give me...some thoughts..ideas..

Upvotes: 0

Views: 204

Answers (4)

Edmond Quinton
Edmond Quinton

Reputation: 1739

One approach that might work for you if you have the necessary database permissions is to use the ‘sp_helptext’ system procedure. With this approach you won’t need to develop a C# app.

The first step is to create a temporary database that will hold the stored procedure content:

USE [master]

GO

CREATE DATABASE StoredProcBackup;

GO

USE [StoredProcBackup]

GO

CREATE TABLE StoredProcedures
(
     [ProcedureName] NVARCHAR(200)  Primary Key
    ,[ProcedureContent] NVARCHAR(MAX)
)

In the above table the StoredProcedures table will hold the names and content of the stored procedures you are trying to script out. The next step is to create a cursor that enumerates the procedure and write the content to the StoredProcedures table in the newly created StoredProcBackup database.

DECLARE @procedureName NVARCHAR(200) ;
DECLARE @procedureContent NVARCHAR(MAX);

DECLARE @procedureContentTable TABLE
(
    [Text] NVARCHAR(MAX)
)

TRUNCATE TABLE [StoredProcBackup].[dbo].[StoredProcedures];


DECLARE procedure_cursor CURSOR FOR   
SELECT ROUTINE_NAME
FROM information_schema.routines 
WHERE routine_type = 'PROCEDURE';

OPEN procedure_cursor;

FETCH NEXT FROM procedure_cursor   
INTO @procedureName;

WHILE @@FETCH_STATUS = 0  
BEGIN  

    SET @procedureContent = '';

    INSERT INTO @procedureContentTable
    EXEC dbo.sp_helptext @procedureName;

    select @procedureContent = coalesce(@procedureContent , '') +  convert(NVARCHAR(MAX),[Text])
    from @procedureContentTable;

    DELETE @procedureContentTable;

    INSERT INTO [StoredProcBackup].[dbo].[StoredProcedures]
    (
        [ProcedureName]
        ,[ProcedureContent]
    )
    VALUES
    (
        @procedureName
        ,@procedureContent
    );



    FETCH NEXT FROM procedure_cursor   
    INTO @procedureName;

END 

CLOSE procedure_cursor;
DEALLOCATE procedure_cursor;

You can now backup the ‘StoredProcBackup’ database and restore on your local server. Once you have a copy on the local server you can simply run the following script to restore the stored procedures.

USE [StoredProcBackup]

DECLARE @procedureContent NVARCHAR(MAX);

DECLARE create_procedure_cursor CURSOR FOR   
SELECT  [ProcedureContent]
FROM    [dbo].[StoredProcedures]

OPEN create_procedure_cursor;

FETCH NEXT FROM create_procedure_cursor   
INTO @procedureContent;

WHILE @@FETCH_STATUS = 0  
BEGIN  

    EXEC (@procedureContent);

    SET @procedureContent = '';

    FETCH NEXT FROM create_procedure_cursor   
    INTO @procedureContent;

END 

CLOSE create_procedure_cursor;
DEALLOCATE create_procedure_cursor;

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32697

You mentioned C# and that's not a bad approach. Or, more generally, MS provides a .NET library called SMO (SQL Management Objects) that makes things like this not so bad. In a comment to another answer, you mentioned the size of the file. Specifically, I'd take a look at the Server and Database objects (and the StoredProcedures collection for the latter). Arbitrarily pick some number of procedures you want to put into a given file and iterate over the list of stored procedures. Once you've put in as many as you want to put in one file, increment the file name and keep scripting.

Upvotes: 0

librata
librata

Reputation: 150

You can just backup the database and restore it again. How big is the database ?

Upvotes: 0

Joe C
Joe C

Reputation: 3993

To transfer stored procedures, functions, views and many other object types I would use the generate script wizard. You can run the generated scripts in the new environment using SSMS.

Upvotes: 1

Related Questions