Category Archives: sql server

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!

Session: T-SQL’s Hidden Support Feature

Today I presented one of my favorite sessions – T-SQL’s Hidden Support Feature – for the DBA Fundamentals group! They’ll put up the recording shortly, but in the meantime I thought I’d post the slide deck and header template:

Download Goodies

Also, here are the resources I point to at the end of the session:

Oh heck, here’s the session abstract, too:

The most effective T-SQL support feature comes installed with every edition of SQL Server, is enabled by default, and costs no overhead. Yet, the vast majority of database administrator underutilize or completely neglect it. That feature’s name is “comments”.

In this session, Microsoft Certified Master Jennifer McCown will demonstrate the various commenting methods that make code supportable. Attendees will learn what’s important in a header comment, use code blocking to edit code, build a comprehensive help system, and explore alternative comment methods in stored procedures, SSIS packages, SSRS reports, and beyond. These methods help prevent errors and reduce troubleshooting.

Thanks for having me, DBA Fundamentals!

#PASSSummit Women in Technology lunch live blog!

It’s the Women in Technology luncheon! I’ll be updating this (at eating) for the next hour or so…let’s go.

Update: And we’re done!

Welcome

Wendy Pastrick, the PASS Director of educational content, wecomes us. It’s the 15th annual PASS Summit WiT luncheon. There are 800 attendees at the lunch today!

She’s introducing the speaker. There will be a QnA session afterward. (This is being live streamed on PASS TV, and the recording will likely be available there afterward.)

He Says. She Says.

Our speaker is Heather Ritchie, Head of Portfolio Marketing and Communication, presenting “He Says. She Says. Bridging communication gaps that prevent great ideas from being heard.” That’s a good premise.

Slide: Where are the gaps? Thinking, expectations, confidence, linguistics, voice.

“Take what I say with a grain of salt. I’m going to make some HUGE generalizations today…there’s going to be exceptions” to every rule. That’s a good disclaimer to keep people from shouting “WAIT BUT NOT ALL MEN/WOMEN” etc.

Heather discussed communication styles – 75% of men tend to evaluate as analytical, while 75% of women have the relational profile.

And now, a discussion of group IQ, and how it was evaluated in one study. Which boils down to a relational mode of thinking (e.g., how often people are interrupted, how they contribute, etc.)

You need “diversity. You need all profiles to build a great team.”

Expectation Gaps

Women face a double bind, where what it means to be a woman (communal, nurturing) and what it means to be a boss (leader) are in opposition. The story of a woman whose new team were asking her basic questions: “Would you ask Steve this question?” “No.” “Then don’t ask me, I’m not your mother.”

Women are two times as likely to get personal criticism in reviews (e.g. , “bossy”, “abrasive”, “aggressive”). This has been my experience, definitely.

Things get more complex for women, in order to be understood properly.

The Confidence Gap

Here’s a video of Cheryl Sandberg, Facebook COO, on negotiating for the Facebook position. (I have also had to learn to negotiate.) “If you don’t negotiate, he won’t value you as much.” But you can’t do it the way men do it, Cheryl says. There are videos on how to do it at women.org.  As a woman you have to tie that into how it’s going to be good for the other side.

Heather says, everyone has doubts. Women express it differently.

“I have never met a woman who’s said I’ve seen this position I want it. I am the best for this. NEVER.” – Carmen Munoz, CEO, Citelum Group.

“Confidence is not a fixed state.” 

Linguistic Differences

“You can geek out a LOT” on the linguistic differences in gender.

“Words have power. How you say things makes a difference.” Heather plays this video, which I first saw from Rie Irish:

Feminine language drives rapport; masculine language drives status. We need both, and both have downsides.

Voicing your ideas

Heather actually worked with a voice coach to improve her volume, and voice in general.

“Women have a natural disadvantage with voice.” (I’d also like to note that girls are far more likely to be told to be quiet and polite, and that does affect how we speak.)

GREAT advice: record your phone meetings, record yourself speaking (like practicing for a session), listen to how you sound. 

We can do better

The “We can do better” slide says:

  1. Adapt the way you communicate your idea for your audience
  2. Build intelligent collaboration practices
  3. Learn how to frame your ideas in a common purpose
  4. Get rid of mindsets that hold you back [“Sorry”, or interrupting women.]
  5. Develop your communication skills: basics, strengths, adaptive

My Commentary

Something about this bothers me. It’s not bad advice, as far as it goes. But I don’t like advice to women, that we should change and bend to the world, if the advice doesn’t come with some serious recognition that things shouldn’t be this way.

That we can work to change attitudes, not to just adapt to what’s here.

Maybe working to change attitudes isn’t the greatest career advice….but maybe it really is. The story from above – “Well don’t ask me, I’m not your mother” – rings better to me.

So absolutely. Work on how you communicate, of course. Get better at collaborating, at framing ideas. All of that. But also: push back.

And a little more…