How to store complete Stored_Procedure content in a table as a record in sql server?

Question

I am working on SSIS, through which I have to copy the complete Stored procedure from one SQL server instance to another instance in a table as a record. How can I achieve that? For eg:

-----sp------

Create PROCEDURE simplesp
AS
SELECT * FROM sample
GO;

-------table----------

select ID,SP_info from SP_Content;

the above table query should return a value like,

ID        SP_info
----  ------------------------------------------------------
1      Create PROCEDURE simplesp AS SELECT * FROM sample GO;

Answer 1

You might be looking for something like this.

SELECT row_number() OVER (ORDER BY OBJECT_NAME(OBJECT_ID)) ID,
       OBJECT_NAME(OBJECT_ID) as Name,
       definition as SP_info
FROM sys.sql_modules
WHERE
    objectproperty(OBJECT_ID, 'IsProcedure') = 1