Need help modifying a SPROC's output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help modifying a SPROC's output

Author

Topic

toddhd
Yak Posting Veteran

55 Posts

Posted - 2005-07-10 : 12:19:04

Someone on this forum (nr) turned me on to this SPROC which outputs the script from a given database object.
http://www.mindsdoor.net/DMO/DMOScripting.html

It works great - my only complaint is that it outputs the script to a text file on the hard drive. I was wondering if there is way to modify it in order to simply return the script, instead of saving it to a file?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-07-10 : 12:33:13

You can output the file.
Where it says
select @sql = 'type ' + @tmpFileName + ' >> ' + @FileName

change it to
select @sql = 'type ' + @tmpFileName
and remove

-- create empty output file
select @sql = 'echo. > ' + @FileName
exec master..xp_cmdshell @sql
and
select @sql = 'echo print ''Create = ' + @SourceObject + ''' >> ' + @FileName
exec master..xp_cmdshell @sql

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2005-07-10 : 12:33:41

You can use some code like this to get the CREATE statements for views, sprocs, etc. (but not tables)


set nocount on
declare @str varchar(100),@id int
declare c cursor for select name,id from sysobjects where xtype = 'V'
open c
fetch next from c into @str,@id
while @@fetch_status = 0
	begin
	select @str as name, text from syscomments where id = @id
	fetch next from c into @str,@id
 	end
close c
deallocate c
set nocount off

You can of course modify it to turn it into a sproc that outputs only for specifice objects.

Sarah Berger MCSD

toddhd
Yak Posting Veteran

55 Posts

Posted - 2005-07-10 : 12:42:32

Thanks again

nr
SQLTeam MVY

12543 Posts

Posted - 2005-07-10 : 12:44:15

To get the script from syscomments you should cater for the script going across pages where it will give invalid statements.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources