Part 1 uses information_Schema.routines view and this is based on sp_helptext declare @sps table ( texts varchar ( 8000 )) insert into @sps select 'sp_helptext ''' + name + '''' from sysobjects where xtype in ( 'p' , 'fn' ) order by xtype , name create table scripts ( sps varchar ( 8000 )) declare @texts varchar ( 1000 ) select @texts = min ( texts ) from @sps while @texts > '' Begin EXEC ( 'insert into scripts(sps) EXEC ' insert into scripts ( sps ) select 'GO' select @texts = min ( texts ) from...(
read more)
Read the complete post at http://sqlserver-qa.net/blogs/t-sql/archive/2007/12/13/2933.aspx