Enterprise Software

Using SQLCMD to execute SQL scripts

If you use the same SQL scripts repeatedly, you can save a lot of time by creating a batch file for them. SQLCMD can help you build those batch files. Steven S. Warren shows you how to use SQLCMD to execute scripts in SQL Server 2005.

IfDo you have a set of SQL scripts that you run all the time? Have you thought about creating a batch file to call these scripts whenever you need them to run? Here is how you would do such a thing.

First, create a folder on your computer and call it something such as scripts and then drop your scripts into the folder (Figure A).

image

Figure A.

Next, create a batch file that points to the script or scripts that you created (Figure B).

image

Figure B.

Your script or scripts can be as easy or as complex as you want. Create a set of scripts to help you in your daily life.

Let's now go ahead and run our batch file (Figure C).

image

Figure C.

Another script I tend to run a lot is a reset permissions script. Figure D. shows the output.

image

Note: You can run a sqlcmd /? to get a listing of all your switches (Figure E).

image

Figure E.

Go ahead and experiment and see what you can come up with. Good Luck!

1 comments
ronwarshawsky
ronwarshawsky

There is a free tool "SQLS*Plus" which is an SQL*Plus for SQL Server. Works with SQL Server 2000/2005 and 2008 Very flexible with data formatting (set lines size, pagesize, etc), variables (&, &&), etc - light years better than isql or sqlcmd I found it at http://www.memfix.com