Scripts Stored Procedures and Functions through query



This query will script the Procedures and Functions

All you have to do is replace DBname by the actual Database Name


exec master..xp_cmdshell
'bcp "Select routine_definition from DBname.information_Schema.routines order by routine_name" queryout "C:\scripts.sql" -c'
 

After it runs successfully, the file C:\scripts.sql will have the scripts

 



Published Monday, August 27, 2007 4:23 PM by Madhivanan

Comments

# re: Scripts Stored Procedures and Functions through query

Wednesday, December 5, 2007 10:07 AM by fernando

good

# Script out Procedures and Functions - Part 2

Thursday, December 13, 2007 1:57 PM by Madhivanan

Part 1 uses information_Schema.routines view and this is based on sp_helptext declare @sps table ( texts

# Script out Procedures and Functions - Part 2

Thursday, December 13, 2007 2:09 PM by SQL Server Transact-SQL (SSQA.net)

Part 1 uses information_Schema.routines view and this is based on sp_helptext declare @sps table ( texts

# re: Scripts Stored Procedures and Functions through query

Tuesday, February 12, 2008 2:48 PM by Jane in London

Oooooh, I like that.  Spent a happy hour playing with bcp, but didn't actually achieve much due to permissions issues.  Learnt something new.  Thanks.