Useful in batch scenarios...
I'm the first to admit that I'm no database guru, but I occasionally have need to do something beyond just a simple SELECT, and I can usually manage to fumble my way through it.
The other day, the thing I was fumbling my way through was restoring a database from a backup a client had sent me. While I was doing it, I ran across a neat little option to sqlcmd.exe that I hadn't seen before, but which I definitely want to remember. It's the -v option, and it lets you pass parameters to your SQL script. In my case, I wanted to pass the current directory, so I could restore the database to files in whatever directory I happened to be running. Well, putting this in a .cmd file does the trick:
sqlcmd -E -i restoredb.sql -v root="%CD%"
Then I can use the root variable in my SQL script. All I have to do is reference it with the $(root) syntax, like this:
RESTORE DATABASE MyDB
FROM DISK = '$(root)\mydb.bak'
MOVE 'mydb_data' to '$(root)\mydb.mdf',