TRUNCATE TABLE master..sysdatabases

...and other neat SQL Server tricks
posts - 51, comments - 60, trackbacks - 14

DOS Rulez All ya SQL Newbies!

As Damian pointed out, you can do a lot of cool things with DOS command-line functions.  Lately I'm particularly enamored of the for command.

The DOS for command works like a for...next loop in Basic, C/C++/C#, Java(Script) etc., only you can have it traverse a filespec (like dir *.txt would), a directory tree, or even the contents of a file.  To modify Damian's example a little, you could actually execute each individual SQL script with the following:

for %a in (*.sql) do osql -E -Sserver <%a

The %a is a DOS replaceable parameter, and it holds the complete file name of each file in the *.sql file spec. for evaluates the osql command for each of them.

And if you haven't already seen it, you can use this to answer an age-old import question:  how do I import ALL of the text files in a folder?  If they are all the same format and all need to go into the same table, it's easy:

for %a in (c:\myfolder\*.txt) do bcp mydatabase..mytable in %a -T -Smyserver -c

I got this from an MVP newsgroup post and I can't remember who posted it, but I have to thank her for posting it!  I have a couple of other cute tricks that I'll post later, in the meantime I recommend you take a look at the Windows Help file (bet you don't even know where it is!) for more info on for.  Also check out find and sort.

Print | posted on Sunday, September 28, 2003 9:17 AM | Filed Under [ Coolness Command Line ]

Feedback

Gravatar

# The For Command: Part 1-1/2

1/22/2005 9:34 AM | TRUNCATE TABLE master..sysdataba
Gravatar

# The For Command: Part 1-1/2

1/22/2005 9:46 AM | TRUNCATE TABLE master..sysdataba
Gravatar

# The For Command: Part 1-1/2 - Deploy Scripts to Multiple Servers

2/2/2005 10:12 PM | TRUNCATE TABLE master..sysdataba
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET