Friday, October 17, 2014

View Default Constraints

A co-worker today couldn't figure out how a column, ostensibly being set to null held a value of 0 instead of null. I looked at the populating code and couldn't find anywhere a null was being replaced with a value of zero, so the most likely candidate was a default constraint on the column. Turned out he wasn't sure how to find default constraints on columns.

The easiest way is to use the sp_help function on the table name, or highlight the table name and hit alt-f1.

exec sp_help myTable

At the bottom, you'll find all the constraints on the table, including default constraints.

If however your table is not part of the dbo schema (as the Aventureworks sample database is fond of doing) you won't be able to use those methods on the table as it assumes the object exists in the 'dbo' schema. Instead, you can access the data via the Dynamic Management Views (DMVs) like this:
    TableName = object_name(c.object_id),
    DefaultConstraintName =,
    ColumnName =,
    DefaultValue = ds.[definition]
from sys.default_constraints ds
inner join sys.columns c
    on ds.parent_object_id = c.object_id
        and ds.parent_column_id = c.column_id

Monday, October 6, 2014

Regular Expressions in SSMS

Several times a year I get asked something like "Can SQL do regular expressions"? The answer is essentially no (I know, I know, CLRs). But while SQL statements can't make sure of regular expressions to search through table data, you CAN use them in the course of creating and editing SQL scripts.

SQL Server Management Studio as well as Visual Studio, which the later versions of SQL Server use as their IDE support regular expressions in find-and-replace operations. If you're wholly unfamiliar with regular expressions, there are tons of resources out there to help you learn. Some good info can be found at I personally bought a book called Introducing Regular Expressions which I found quite useful. It's not my intent to teach regular expression here, but to show you how to find a regex character you need and give you some find-and-replace regular expressions I use almost daily.

First things first, SQL Server uses some funky notation for some of its special characters. I found it particularly difficult to find this page on Google as well, but this is, as close to I can find, the definitive source describing the regex syntax used in SSMS and VS

Search Text With Regular Expressions

Commonly used expressions

Again, I don't want to give a comprehensive overview of regular expressions in SQL, but show you some of the tricks I use a lot which can save a lot of time in editing procedures. 
  • :a (alphanumeric)
    • I probably use :a more than anything else. It matches any alphanumeric character which is great of SQL objects that use camel case. Add a + or * to the end of it to quickly find a list of column names.
    • Example:
      • :a+
      • Matches any string of 1 to n alphanumeric characters
  • :d (digit)
    • To be honest I rarely use this one, but it's another simple useful one to identify any digit between 0 and 9. Again, you can modify it with a + or * to find strings of numbers.
    • Example
      • :d*
      • Matches any string of 0 to n digits
  • . (Any character)
    • As the description suggests, a period "." will match any single character; be it white space, a number a letter or so forth. I don't think it will capture line breaks.
  • [] (character set)
    • Another one I use all the time. Those of you who read my comment about easily identifying object names may have pointed out that object names can contain underscores, variables are declared with an @ sign, and temp tables are declared with a # sign. Build a character set inside brackets to allow what you need.
    • Note that you CAN use special characters in your character sets such as :a and :d
    • Example
      • [:d@]
      • Matches the @ sign or any digit
  • {} (Tagged Expression)
    • The syntax for this is, from what I hear, a little different from some other regex dialects. I don't know how many you can have nested in a single regex, but I've never had to use more than 2 or 3 at a time.
    • How these work in find-and-replace is each tagged expression maps to a number which can be accessed in the "replace" bar by a slash "\" and the number of the tagged expression.
    • To figure out what the number of the expression is, start counting from the left-most open curly bracket in ward (starting at 1). 
    • Hello {{\@}:a+}
      • \2 matches the @ character
      • \1 matches the @ character AND a string of one or more alpha numeric characters
    • \0 always represents the entire regular expression
  • \ (Escape)
    • Always a good one to know. slash "\" escapes a special character
    • Example:
      • \@


Here are some example regular expression similar (if not verbatim) to what I use quite often.I don't show it in my screenshots, but generally, you'll want to select "use selection" from the "look in" dropdown or you will apply it to the whole script. Also, check the box under "find options" that says "Use:" and then select "Regular Expressions" from the dropdown.
  • Select variables as columns
  • Injecting a string into a set of column names
  • Aliasing columns
  •  Make a serialized string easier to read

While this isn't a comprehensive list of what you can do with regular expressions and find-and-replace, hopefully it gives you some good ideas of ways to speed up your work. Combined with the Square Marquee  I talked about in a previous post, you can do some pretty creative things to speed up editing and creation of stored procedures.

If you have regular expression you use regularly (get it?) or any questions, please feel free to leave me a comment below.

Friday, October 3, 2014

Square marquee

For those of you who may not have known, there’s a cool trick you can do in Visual Studio or SSMS; the alt-drag marquee. Many of these functions can be handled with fine and replace with or without regular expressions, but sometimes those are more trouble than it’s worth.

The basics of alt-drag are this. Say I have a block of text, such as a comment block of a procedure which I want to replace all at once. Hold alt and drag to the bottom right, and you get a marquee area which respects x/y coordinates rather than just lines and indentation.

Now the cool part. If you’ve ever used copy-paste with these marquees, you’ll find they work… oddly. However like any good Jedi, you can turn your enemies weakness into your strength.

Say for example, I forgot to type select statements for a derived table like this:

You could use find replace to replace “all” with “all select’, you could use a series of copy/paste commands and down arrow keys, or this:

Create a zero-width vertical marquee and start typing. Every text line which is crossed by the marquee will begin typing there (it works with ctrl-v_ as well.

Note, you can also hold alt+shift and use the arrow keys to make a square marquee as well.