SQL Server Performance Forum – Threads Archive

nesting script files

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)?

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

Using SQLCMD you can achieve this as mentioned above.
http://msdn2.microsoft.com/en-us/library/ms165702(SQL.90).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. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Satya, this works also in SQL Server 2000 with OSQL. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Ahem, yes [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] as originator mentioned SQL 2005 hence I said SQLCMD… Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of 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.</center>

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

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
…

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.

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.

]]>