SQL Server: How to extract comments from stored procedures for deployment

Question

We have lots of stored procedures which all contain a lot of comments.
Is there a way to extract the comments from the stored procedures for deployment so the users can't see them when they modify a stored procedure with SQL Server Management Studio?

Note: We're using SQL Server 2008.

Answer 1

You can use the WITH ENCRYPTION option:

CREATE PROCEDURE dbo.foo 
WITH ENCRYPTION 
AS 
BEGIN 
    SELECT 'foo' 
--Try this just to make sure
END

before that make sure you keep the logic of the stored procedure in a safe place, since you won't have easy access to the procedure's code once you've saved it.

After that the user can't use Enterprise Manager or sp_helptext to get the Source Code but this can be cracked...

Answer 2

Encryption is great but if you need to allow users to make changes to stored procedures (as your initial question suggests) then encryption isn't your answer. It'll hide the comments AND the code from the users.

If you want to leave the code visible but remove comments, you might want to use Management Studio to script out all your stored procedures, then use a macro/text editor like UltraEdit to find and remove all comments, and then run that script again to rebuild all the comment-less stored procedures.