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