SQL Server Performance

nesting script files

Discussion in 'Getting Started' started by danareed, Jun 26, 2007.

  1. danareed New Member

    While developing a new db and changing the schema, I keep clobbering dependent objects, like existing views, procedures, triggers, etc. So I want to create a set of .sql script files, one for each database object, and keep them in separate Windows folders (one for procedures, one for functions, etc.). I want each folder to have one file containing the names of all the script files in that folder, and to be able to "invoke" that one file to execute all the individual scripts named in it. Finally, I'd like to have one "master" file that invokves all the individual sub-files, so I can recompile the world by invoking one file.

    Note that I DON'T want to put all the database objects in one gigantic script file. The key is being able to invoke files from files, nesting them. I did this years ago in Oracle; is there a way to do this in SQL Server (2005)?
  2. FrankKalis Moderator

    Not sure if I understand you correctly, but sure you can call other scripts from within a master script.


    master_script.sql
    --do some smart stuff
    :r full path to the first nested script
    :r full path to the nth nested script

    For an example, check this out:http://groups.google.de/group/microsoft.public.sqlserver.programming/msg/d5ee5cd5ddcaf1e4

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  3. satya Moderator

  4. FrankKalis Moderator

    Satya,<br />this works also in SQL Server 2000 with OSQL. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. satya Moderator

    Ahem, yes [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] as originator mentioned SQL 2005 hence I said SQLCMD...<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  6. FrankKalis Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  7. danareed New Member

    Thanks everyone; the ":r" was exactly what I was looking for.
    This is what I end up with:

    sqlcmd -i all.sql (default to local server & Windows authentication)

    all.sql
    -------
    :r procedures.sql
    :r functions.sql
    ...

    procedures.sql
    --------------
    :r proc1.sql (proc1.sql contains conditional DROP & CREATE for proc1)
    :r proc2.sql
    ...

    functions.sql
    -------------
    :r func1.sql (func1.sql contains conditional DROP & CREATE for func1)
    :r func2.sql
    ...
  8. satya Moderator

    Appreciate your feedback in this case, as it helps others who are looking for such solution.

    Tip: You can keep all these into a DOS batch file and call as and when necessary.

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

    Yes, Satya, that's just what I did; I created a .bat file containing the command "sqlcmd -i all.sql" that I can invoke by double-clicking it in Windows explorer.

Share This Page