Category Archives: sqlserverpedia-syndication

Absolute computer fundamentals: the command line!

So I’ve spoken to more than one DBA who is not familiar with using the command prompt (aka command line) – or as we old-schoolers still accidentally call it, “DOS”. It’s not really DOS any  more, of course, but it still uses the old commands and has the same basic look:

An empty command prompt window

From here, you can move around, change things, and explore. The command prompt is context dependent, meaning – just like in a file explorer window – you can see and affect things (mostly) in the folder (aka context) that you’re IN. In the image above, we’re on the drive root: C:\.

Why should it matter to use the command prompt? Well for one thing, certain things are just easier and/or faster via the command prompt, than it is when you use the Windows GUI.

For another thing, PowerShell accepts all the basic command prompt commands. I definitely think you need to learn PowerShell, so you may as well double up and learn your basic command prompt commands!

Launch your command prompt window: Of course you can find “command prompt” in the Windows menu. But you can also use Windows-R to get the Run dialogue, and type cmd. That’s how Jen does it, kids!

Basic Commands

You’re in the command prompt. Just like being in a File Explorer window, the command prompt has a context. You’ll likely start out in your own user folder. For example, C:\Users\jmccown. Let’s move around a little, shall we?

  • cd .. – Go up one directory level. (“..” always means “up one directory level. So if we wanted we could type ..\.. to go up TWO directory levels, and so on.)
  • cd \ – Go to the root directory. In this case, we’re in C:\, so wherever we are in the folder structure, “cd \” will take us back to the root of C:\.
  • cd <folder name> – Change directory to <folder name>. This is like double clicking on a visible folder in a file explorer window.
  • dir – Get a listing of what’s in this directory.
  • dir p* – Get a listing of everything in the current context (the folder we’re in) that starts with the letter P.
  • dir P* /A:D* – Get a listing of just the directories in the current context that starts with the letter P.  How did I know about that /A flag? See “Help”, below.
  • cd “c:\Program Files\Microsoft Office” – Move to the Microsoft Office folder, no matter where I’m starting from. See, I’ve given the command prompt a full path, so it’s not context dependent! Also note that I enclose the path in quotes, because the command prompt depends on spaces to tell one element from another. If you tried this without quotes, you’d get an error.
  • L: – change to the L drive. Note we do not cd (change directory), because L isn’t a directory!
  • Help <command> – Get help on the <command> named. This can be very, well, helpful!

Getting Help

Remember that /A attribute from above? We can find out what options we have, and what other flags are available for each command. Just use Help dir, or whatever command you’re interested in. For example:

command prompt with "help dir" results

You can also look up or study command prompt commands on TechNet and elsewhere.

More commands, and batch files

Well, we can rename (or ren) files and folders, move, copy, clear the screen (cls), direct output to a file (>, which creates or overwrites a file, and >>, which creates or appends to a file), and exit the command prompt.

You can also run batch files, which are just saved lists of commands. If I save the following list of commands as file.bat

cd \
cd temp
dir *.txt >> output.txt

…then I can run it from the command prompt, using file.bat, to get list of TXT files in c:\temp written to a new file called output.txt (or appended, if the file already exists).

This really is a bare bones introduction to the command prompt. But I really think you should familiarize yourself with these basics – play around with them. Just be careful not to rename, delete, or overwrite anything important!

RegEx in SSMS: A gift for quick code

I had yet another example of how beautiful Regular Expressions can be in SQL coding today, and so I thought I’d share.

What I HAD was a set of DELETE statements that might or might not have been executed recently:

DELETE FROM dbo.Table1 WHERE val = 1
DELETE FROM dbo.Table2 WHERE val = 1
DELETE FROM dbo.Table3 WHERE val = 1
DELETE FROM dbo.Table4 WHERE val = 1
DELETE FROM dbo.Table5 WHERE val = 1
DELETE FROM dbo.Table6 WHERE val = 1 and x = y;
DELETE FROM dbo.Table7 WHERE val = 1
DELETE FROM dbo.Table8 WHERE val = 1 and val2 in (select val from Table8)
DELETE FROM dbo.Table9 WHERE val = 1
DELETE FROM dbo.Table10 WHERE val = 1
DELETE FROM dbo.Table11 WHERE val = 1
DELETE FROM dbo.Table12 WHERE val = 1
DELETE FROM dbo.Table13 WHERE val = 1
DELETE FROM dbo.Table14 WHERE val = 1

What I needed was to be able to check each one of those tables, and see if any of the should-be-deleted rows still exist. Of course, I can certainly type this all out by hand…

IF EXISTS (SELECT * FROM dbo.Table1 WHERE val = 1) SELECT 'Table1 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table2 WHERE val = 1) SELECT 'Table2 has undeleted rows!' as TableName;

*Sigh*. I’m already  bored. And in reality, I had 45 of these statements, not 15. I’m not a big fan of repetitive, monkey-button work.

RegEx to the Rescue!

We can turn those statements into a formula, and do a replace-all! That’s what RegEx is great at. We can look at a string like that DELETE statement, and formulize it as pseudocode:

Start of line + 'DELETE FROM ' + table name + ' WHERE ' + where clause + end of line.

With just a little bit of know-how, turn this into a proper (SSMS flavor of) regular expression:

^DELETE FROM {.+} WHERE {.+}$

Here’s a quick key to these expressions:

  • ^ – start of line
  • {} – tagged expression
  • . – any character
  • * – one or more (in this case, one or more of “any character”)
  • $ – end of line

See? Direct translation! I’m strictly including start of line and end of line to be sure there’s no confusion whatever…I want this formula to apply to a single line of text.

Now, what I want to change each of these to is that IF EXISTS statement, which we can formulize like this:

'IF EXISTS (SELECT * FROM ' + table name + ' WHERE ' + where clause + ') SELECT ''' + table name + ' has undeleted rows!'' as TableName;'

Just a little more know-how, and we get this regular expression:

IF EXISTS (SELECT * FROM \1 WHERE \2) SELECT '\1 has undeleted rows!' as TableName;

Here’s a quick key to these expressions:

  • \1 – First tagged expression (in this case, we’d tagged the characters between FROM and WHERE, which means we tagged the table name)
  • \2 – Second tagged expression (the letters between WHERE and end of line, the where clause)

Note that you don’t have to put the beginning of line/end of line markers as part of the Replace With text.

Here’s what all this looks like in SSMS. Remember to select “Use Regular Expressions” in the find and replace dialogue!

SSMS Find and Replace window with regular expressions

And here are the results of the replace all, which require just a touch of manual editing (notice the semicolon in the middle of the Table6 statement). A touch of manual editing is WAY better than typing out 45 of these statements by hand!

IF EXISTS (SELECT * FROM dbo.Table1 WHERE val = 1) SELECT 'dbo.Table1 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table2 WHERE val = 1) SELECT 'dbo.Table2 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table3 WHERE val = 1) SELECT 'dbo.Table3 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table4 WHERE val = 1) SELECT 'dbo.Table4 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table5 WHERE val = 1) SELECT 'dbo.Table5 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table6 WHERE val = 1 and x = y;) SELECT 'dbo.Table6 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table7 WHERE val = 1) SELECT 'dbo.Table7 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table8 WHERE val = 1 and val2 in (select val from Table8)) SELECT 'dbo.Table8 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table9 WHERE val = 1) SELECT 'dbo.Table9 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table10 WHERE val = 1) SELECT 'dbo.Table10 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table11 WHERE val = 1) SELECT 'dbo.Table11 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table12 WHERE val = 1) SELECT 'dbo.Table12 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table13 WHERE val = 1) SELECT 'dbo.Table13 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table14 WHERE val = 1) SELECT 'dbo.Table14 has undeleted rows!' as TableName;

See there? If we know just a few symbols, and take it step-by-step, we can save ourselves a ton of time! And this is but one example of an infinite number of uses for RegEx in SSMS.

Watch our RegEx videos on MidnightDBA.com, especially the session given at CACTUSS 2017!

PowerShell for fun (and profit?)

PowerShell is useful for a great many things, but I like it best (on a day-to-day basis) for the little things that it simplifies. For example:

  1. Windows-R to get a Run dialogue.
  2. Type powershell.exe restart-computer
  3. And your computer restarts!

I like this a good deal better than all the hunting around with a mouse to find the durn power icon, wherever they’ve moved it THIS week.

I also dig being in a PowerShell window and being able to open the output file I just generated, with the command notepad.exe c:\temp\filename.txt.  I am nothing if not lazy efficient.

What other fripperies can we use in PowerShell?

So I got to thinking…PowerShell must know where Notepad.exe is – namely, in c:\windows\system32. So it must (of course)  be able to run other system EXEs really easily.

I began scrolling through the extensive list of apps in the sys32 folder, and stumbled across:

cmd

*snortlaugh*

Sure enough, you can “launch” the command window from within PowerShell. It looks a little like this:

poshcmd

Yes, this is silly, and mostly useless. But it’s fun. Or more to the point, funny.

Okay but seriously, why are we talking about this?

Mostly because it amused me. But also to remind you that there’s good work to be simplified with PowerShell, and sometimes some good fun to be had, too.

You can, for example:

  • Launch the control panel with control.exe
  • Or MS Paint, mspaint.exe (I’m not the only one that still uses this!)
  • Or Magnify, magnify.exe

If you’d like to learn PowerShell, check out the Beginning PowerShell session on our Events page, and the PowerShell tutorials on MidnightDBA.com.

Happy days,
Jen