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!