Backing up Stored procedures

Is there an easy simple way to backup all my personal Stored Procedures.

In the stored procedures collection, my stored procedures start with "DEF".

I would like to have the stored procedures places as text files in a personal backup folder.

Is this possible?

I have the query to get my SP's:

SELECT

*

FROM

SYSOBJECTS

WHERE

xtype='p'AND NameLIKE'DEF%'

ORDER

BY NAME

Next step is how to get the code inside each stored procedure to be exported to a .txt file.

How can I do this?

|||

You should do it at design time if possible.

You can try to send your stored procedure code as attachments in e-mail and next save this attachments or you can get stored procedure code using system stored procedure and save result to flat file output using SSIS package, You can also try to use Bulk copy?. You cannot save to file directly from T-SQL

Thanks

|||

You should do it at design time if possible.

You can try to send your stored procedure code as attachments in e-mail and next save this attachments or you can get stored procedure code using system stored procedure and save result to flat file output using SSIS package, You can also try to use Bulk copy?. You cannot save to file directly from T-SQL except if it will be CLR stored procedure which is probably best way to do it.

Thanks

|||What do you mean by CLR stored procedure ?|||

CLR stored procedure is new for SQL 2005 and allow you to create stored procedure or function in .net language, so you can access database and any available .net stuff, and good thing is that you can run it on SQL serer as standard SQL stored procedure (in the past you had extended stored procedures created in C++).

Thanks

|||

(1) You can also generate the scripts from the DB, Right click on DB -> All Tasks -> Generate Scripts.

(2) You can get the text for a stored proc from syscomments.

(3) You can probably write some application to get the text from syscomments and save it to a file.

|||

With the following query, I get all my SP and the text in the query result.

=====

USE

PDSNT

SELECT

name,text

FROM

sysobjects

INNER

JOINsyscomments

ON

sysobjects.id=syscomments.id

WHERE

xtype='p'AND NameLIKE'DEF%'

ORDER

BY NAME

=====

Next step would be to export them to .xml or .txt file.

Anyone can help?