Generate all user stored procedures in all dbs

--------------------------------------------------------------------------------------------------------
--Generate all user stored procedures
--Author Wesley D. Brown
--Date 5.24.2004
--Loops through a list of all user stored procedures in all databases
--ignores dt_ procs added by VS.NET feel free to comment out
--If not owned by DBO issues a sp_changeobjectowner assuming dbo as the owner this is assumed needed
--due to you the DBA being dbo of the database and not logged in as the user that the proc needs
--to be owned by feel free to comment it out.
--------------------------------------------------------------------------------------------------------
set nocount on
DECLARE @procname varchar(60)
declare @sproc varchar(255)
declare @cnt int
declare @user varchar(255)
DECLARE @bcp AS VARCHAR(8000)
DECLARE @Status AS INT
declare @dbname varchar(255)
declare @server_name varchar(255)
declare @cmd varchar(8000)
declare @owner varchar(255)
declare @path varchar(500)

set @server_name = cast(serverproperty('servername') as varchar(255))
--server name won't work on MSDE version of sql replace with @@SERVERNAME
set @path = '\\<servername>\spmigration\'
--path to save to UNC works just fine
select @user = loginame from master.dbo.sysprocesses where spid = @@SPID
--pulls the login name of the current user to fill out some of the self documentation

DECLARE dbperm CURSOR
READ_ONLY
FOR select name from master.dbo.sysdatabases order by name

OPEN dbperm

FETCH NEXT FROM dbperm INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN

		create table #sp_names
		(
		sp_name varchar(255)
		)
		insert into #sp_names
		exec('select '''+@dbname+'''+''.''+a.name+''.''+b.name from '+@dbname+'.dbo.sysobjects b
		inner join
		'+@dbname+'.dbo.sysusers a
		on
		b.uid = a.uid
		where xtype = ''P'' and category <> 2 and b.name not like ''r_iR%'' order by '''+@dbname+'''+''.''+a.name+''.''+b.name')

		DECLARE innerproc CURSOR
		READ_ONLY
		FOR 
		select 
			sp_name
		from
			#sp_names
		ORDER BY
			sp_name
		
		OPEN innerproc
		
		create table ##osql_holding
		(
			sid int IDENTITY(1,1),
			text varchar(255)
		)
		insert into ##osql_holding (text)
		values('declare @cmd varchar(8000)')
	
		FETCH NEXT FROM innerproc INTO @procname
		WHILE (@@fetch_status <> -1)
		BEGIN
			IF (@@fetch_status <> -2)
			BEGIN
			set @cnt = 1
		
			create table ##sp_holding
			(
			sid int IDENTITY(1,1),
			text varchar(255)
			)
		
			insert into ##sp_holding (text)
			VALUES('-- Server Name: ' + @server_name)
			insert into ##sp_holding (text)
			VALUES('-- Database Name: ' + @dbname)
			insert into ##sp_holding (text)
			VALUES('-- Procedure Name: ' + @procname)
			insert into ##sp_holding (text)
			VALUES('-- Date: ' + CONVERT(VARCHAR, GETDATE()))
			insert into ##sp_holding (text)
			VALUES('-- Stored prodecure generated automatically by user ' + @user)
			insert into ##sp_holding (text)
			VALUES('GO  ')
			set @cmd = @dbname+'.dbo.sp_helptext '+''''+@procname+''''
			insert into ##sp_holding (text)
			exec (@cmd)
			insert into ##sp_holding (text)
			VALUES('GO  ')
			if @procname not like '%.dbo%'
			begin
				select @owner = right(substring(@procname,charindex('.',@procname,0),charindex('.',@procname,(charindex('.',@procname)))),len(substring(@procname,charindex('.',@procname,0),charindex('.',@procname,(charindex('.',@procname)))))-1)
				select @procname = replace(@procname,(substring(@procname,charindex('.',@procname,0),charindex('.',@procname,(charindex('.',@procname))))),'.dbo')
				insert into ##sp_holding (text)
				VALUES('EXEC sp_changeobjectowner '''+@procname+''', '''+@owner+'''')
			end

			delete from ##sp_holding where sid not in(SELECT sid FROM ##sp_holding where len(ltrim(rtrim(replace(replace(text,char(13),''),char(10),'')))) > 0)
		
			update ##sp_holding
			set text = ltrim(rtrim(replace(replace(text,char(13),''),char(10),'')))
		
				PRINT ''
				PRINT 'Generating SQL'
				
				SELECT @bcp = 'bcp "SELECT rtrim(text) FROM ' + @dbname + '.dbo.##sp_holding" QUERYOUT "'+@path+'StoredProcs_' + @server_name +'_'+ @procname+ '.sql" -T -c'
				EXEC @Status = master.dbo.xp_cmdshell @bcp, no_output
		
				IF @Status <> 0
				BEGIN
					PRINT 'An error ocurred while generating the SQL file.'
				END 
				ELSE 
				begin
					set @bcp = 'set @cmd = ''osql -S '+@server_name+' -E -d '+@dbname+' -i "'+@path+'StoredProcs_' + @server_name +'_'+ @procname+  '.sql"'''
					PRINT ''+@path+'StoredProcs_' + @server_name +'_'+ @procname+  '.sql file generated succesfully.'
					insert into ##osql_holding values(@bcp)
					insert into ##osql_holding values('exec master..xp_cmdshell @cmd')
				end
		
			drop table ##sp_holding
			END
			FETCH NEXT FROM innerproc INTO @procname
		END
		CLOSE innerproc
		DEALLOCATE innerproc
		if (select count(*) from ##osql_holding) > 1
		begin
			SELECT @bcp = 'bcp "SELECT rtrim(text) FROM ' + @dbname + '.dbo.##osql_holding" QUERYOUT "'+@path+'Restore_StoredProcs_'+ @server_name+'_'+ @dbname+'.sql" -T -c'
			EXEC @Status = master.dbo.xp_cmdshell @bcp, no_output
					IF @Status <> 0
					BEGIN
						PRINT 'An error ocurred while generating the SQL file.'
					END 
					ELSE 
					begin
						PRINT ''+@path+'Restore_StoredProcs_' + @server_name+'_'+ @dbname+'.sql file generated succesfully.'
					end
		end
		drop table ##osql_holding
		drop table #sp_names
	END
	FETCH NEXT FROM dbperm INTO @dbname
END
CLOSE dbperm
DEALLOCATE dbperm

set nocount off