January 31, 2007 at 10:18 am
Is there a way to Generate SQL Scripts for Jobs and/or other objects in my server without using EM (i.e. thru QA or OSQL)? Is ther a system sp for this?
January 31, 2007 at 10:41 am
You have to use SQLDMO (this is what EM uses). There is no T-SQL way to do this without writing the entire process yourself.
Look up "Script Method" in Books Online for information and examples of how to do it.
January 31, 2007 at 11:39 am
For programmatic objects (functions, triggers, stored procedures) you can use sp_helptext. It's not as good, but it's pretty good. Change your results to text rather than grid and then do something like:
sp_helptext p_my_stored_procedure
January 31, 2007 at 11:46 am
If you do that, just be aware that if someone uses sp_rename to rename a stored procedure, the text of the procedure doesn't get changed. Therefore, it is possible that the "Create Procedure" line does not actually match the name of the procedure.
Here, we do not permit the use of sp_rename. We require that the object be created new with the new name and the old version dropped.
January 31, 2007 at 11:59 am
Robert... good point. Honestly I've never been a fan of sp_rename, or even ALTER statements. If possible I usually drop/recreate objects. But that's something good to take into account.
February 1, 2007 at 7:14 am
Try NigelRivett.net, who has some sample routines on his site. Also try Googling 'Jobserver class', for more details on how to use it.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 1, 2007 at 7:53 am
Have you tried scptxfr.exe? Take a look at the following link.
February 1, 2007 at 8:24 am
Just be aware that neither scptxfr.exe nor SQLDMO are aware of the new datatypes and such in SQL 2005. SQLDMO will work with SQL 2005 (for backwards compatibilty) but it thinks that the new data types of varchar(max), nvarchar(max), varbinary(max), and XML all have a defined size of -1. So the script SQLDMO generates may require some modifying before running it.
I don't know if scrtxfr would fail to work or if it would need edits of the script output or what. I haven't tried it.
SQL 2005 does have a new language called SMO that replaces SQLDMO that is easier to work with.
February 1, 2007 at 8:32 am
I have this setup to script ALL of my databases to text files nightly. I then zip the files and archive them. That way I have a snapshot of what all of my procedures were everyday. Text files are highly compressable so it doesn't take up much space.
command:
exec master..sp_MSforeachdb "exec master..sp_ScriptDatabase ?"
stored procedure:
CREATE proc sp_ScriptDatabase @dbname sysname
as
declare @command varchar(1000)
declare @texttime varchar(10)
set @texttime = convert(varchar, getdate(), 102)
set @command = '"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s MILLSFT2 /I /q /G /d ' + @dbname + ' /f E:\BACKUP\VACnightly1\'+LTRIM(RTRIM(@@SERVERNAME))+'_' + @dbname + '_' + @texttime + '.txt /r'
print @command
exec master..xp_cmdshell @command
I don't claim to be the original one who wrote this. I got it off a forum about a year ago. (too long ago to give credit to who gave it to me)
February 1, 2007 at 9:56 am
Don't forget that DMO is now called SMO in SQL 2005. With it, it shouldn't be too hard to come up with a CLR assembly which can be called from TSQL. It can be made to one object, then you can loop through and script what you need.
I'm actually midstream in creating such an object. The trickiest part is how to manage the myriad of scripting options without having a large number of input parameters.
Paul
- Paul
http://paulpaivasql.blogspot.com/
February 5, 2007 at 9:02 am
Thanks everyone for all your inputs. "scptxfr.exe" is very helpful for the database objects. I'm still looking at DMO for the repliactions, jobs.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply