Generating script per object | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Generating script per object

Hi,
I want to generate Script for 4 Stored Procedures at a time in SQL SERVER 2005 as 4 seperate files. In SQL SERVER 2000 there is a option
-Script file per object, using which we can generate script only once for all the 4 sps as 4 seperate files
Is there any such option in 2005? Please suggest. Thanks in advance Ram
Thats by default in SQL 2005, see BIll’s bloghttp://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I was also looking for some info on this one too. I don’t know why Microsoft has eliminated this very useful feature in SQL 2005. It is ALL or NOTHING when scripting in 2005! It’s a bummer![}<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Name<br />——— <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">
Four procedures to one file!!!
Then you have to write your own script…
But you can take the help of new function OBJECT_DEFINITION ( object_id )
to do this… You can loop through sysobjects and write to 4 procedure output to single table then copy that to a file… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Sure, it can be done one way or the other. BUT, it was much easier to script individual object in 2000 without going around these tricks, that’s the difference. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Name<br />——— <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">
Still you can do in 2005 scptxfr.exe comes with sql server 2000…
It works fine in 2005 too…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Provided you have had SQL 2000 tools installed on SQL 2005 machine, as alone it will not work. This will also work if you have upgraded the databases from SQl2k version too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I believe you don’t need to install 2000 tools on 2005 machine…
Just copying the exe file should work….
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
FYI — If you install SP2 the Script to multiple files now works.
quote:Originally posted by MohammedU Still you can do in 2005 scptxfr.exe comes with sql server 2000…
It works fine in 2005 too…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

Not Sure where can you find this file.. I have SQL SERVER 2000 and 2005 both installed on my machine, I am not able to find this file…though..!! Thanks Chirag http://chirikworld.blogspot.com/
In upgrade folder…
C:program FilesMicrosoft SQL ServerMSSQLUpgrade MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by MohammedU</i><br /><br />In upgrade folder…<br />C:program FilesMicrosoft SQL ServerMSSQLUpgrade<br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Not Sure, I dont have any folder called as MSSQL nor Upgrade [<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />].. may be when you Install both SQL SERVER 2000 and 2005, it must overinding those files.<br /><br /><br /><br />Chirag<br /><br /<a target="_blank" href=http://chirikworld.blogspot.com/>http://chirikworld.blogspot.com/</a>
On my machine, I have sql server 2000 and 2005 installed… and I have this folder…
Every 2000 installation should have the folder unless, it is deleted…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
The installation of SQL 2005 will not overwrite any of the SQL2000 files in this case, as you need to choose as a seperate instance and specifying same directory would do this harm. Chirag
Have you chosen to upgrade from 2000 to 2005? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>