MSSQL: How do you script Stored Procedure creation with code?

Question

I am trying to query for a list of stored procedure definitions using information_schema.routines that exist in one database but not in another.

SELECT 
 t1.Routine_Definition
FROM
 [server1].MyDatabase.INFORMATION_SCHEMA.Routines t1 
LEFT JOIN
 [server2].MyDatabase.INFORMATION_SCHEMA.Routines t2 ON t1.Routine_Name = t2.Routine_Name
WHERE
 t2.Routine_Name is null

This gives me the query definitions in a single line so when I have a comment like this

--Some comment
SELECT Column
FROM Somewhere

The SQL gets commented out and I cannot use the definition to create the SP.

How to I parse this back with the proper line breaks?
or
Is there a better way to get these scripts (using code)?

Answer 1

I think the easiest way of getting your stored procedures is to use the Import/Export utility that is built into SQL Server Management Studio. From there you can export your Stored Procedure Objects into the code window or to a file that you can immediately run.

Answer 2

The stored procedure is only displayed on one line in Management Studio. If you run the query with results to text, or use the following, you will get the correct line breaks:

declare @sql varchar(8000) -- varchar(max) in SQL 2005+

SELECT 
        @sql = t1.Routine_Definition
FROM
        INFORMATION_SCHEMA.Routines t1 

print @sql

Answer 3

DECLARE MY_CURSOR Cursor
FOR

SELECT 
        t1.Routine_Definition
FROM
        [server1].MyDatabase.INFORMATION_SCHEMA.Routines t1 
LEFT JOIN
        [server2].MyDatabase.INFORMATION_SCHEMA.Routines t2 ON t1.Routine_Name = t2.Routine_Name
WHERE
        t2.Routine_Name is null AND
        LEN(t1.Routine_Definition) < 4000



Open My_Cursor
DECLARE @sql VARCHAR(MAX) 

FETCH NEXT FROM MY_Cursor INTO @sql
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)

Print @sql

FETCH NEXT FROM MY_CURSOR INTO @sql
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

Here is how I implemented ck's solution...

the INFORMATION_SCHEMA view only returns the first 4000 characters in the definition. (Ideally you wont have SP that are that long)You will want to script those manually or some other way.