SQL Server Performance

Generating script per object

Discussion in 'SQL Server 2005 General Developer Questions' started by ramdotnet, May 3, 2007.

  1. ramdotnet New Member

    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
  2. satya Moderator

  3. DilliGrg Member

    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">
  4. MohammedU New Member

    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.
  5. DilliGrg Member

    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">
  6. MohammedU New Member

    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.
  7. satya Moderator

    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.
  8. MohammedU New Member

    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.
  9. Haywood New Member

    FYI -- If you install SP2 the Script to multiple files now works.
  10. chiragkhabaria New Member

    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/
  11. MohammedU New Member

    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.
  12. chiragkhabaria New Member

    <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>
  13. MohammedU New Member

    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.
  14. satya Moderator

    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.

Share This Page