In this post i am sharing my experience in executing SQL scripts using the utility SQLCMD of SQL-SERVER 2005. In Sql-Server 2000 osql was used to achieve this.
In projects we come across situation wherein we need to decide about the solution of how to run our database scripts. One way is to Generate Scripts in Sql-Server management studio and save these as .sql files and running from the database server. This post is going to explain a similar approach for this.
Consider for e.g we had created separate scripts for dropping tables,constraints etc., table creation scripts, stored procedure scripts all compiled in separate .sql files. Instead of running these .sql files separately in Management Studio we can create a separate master sql script .sql wherein we could call all the child scripts using sqlcmd command.
The below snapshot shows how to achieve this.
The command used to call .sql file is :r. Before you execute you need to enable SQLCMD mode either from toolbar or from Query Menu. After :r command pass the .SQL path with file name within quotes.
One weird this i found is when we are executing stored procedures written as .SQL files if it does not find an GO statement at the end of the first .SQL file it would take the script from the other .SQL file till GO statement and appends that script at the end of the first stored procedure.
Hope this post helps. Provide your comments.
Happy Coding!
Feedback
This sounds like the hard way of doing things. Can you explain why you are using files over SP?
The solution i talked about was running scripts when moving database to production or so. The reason i prefer SQL over SP is easy to run scripts when moving it production. Suppose you have 100 SP in your project and how do u deploy it in the server? You cannot open all the SP's over there and run each one by one. This SQL file is nothing but SP only saved as .SQL file. Even for modification people can take the latest SQL file and modify the SP inside it and run the SQL file.
-i \\
P.S. If the file path has spaces then we need to include them within quotes. Let me know if that works out.