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 27 August 2007 16:23 by Madhivanan
Filed under: , , ,

Comments

# re: Scripts Stored Procedures and Functions through query

05 December 2007 10:07 by fernando

good

# Script out Procedures and Functions - Part 2

13 December 2007 13:57 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

13 December 2007 14:09 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

12 February 2008 14:48 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.