Quick Tips–SQL Prompt Qualifying Columns
I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love the tool and had a copy before Red Gate bought the technology from the original developer. Recently I’ve run into a few people that weren’t aware of some of the ways in which it can help you. This is a quick look at one of the ways I use SQL Prompt.
One of the things that’s a good programming practice for T-SQL is to qualify your columns. Imagine that I have this query:
Note that my column names are listed with just the column name and don’t include the table from which they come. Not a big deal here, but as I enhance this code over time, I may add another table to a join, perhaps one that includes BusinessEntityID in it. In that case, I’ll get an ambiguous column error, and a squiggly in SSMS (shown below).
SQL Prompt tries to make writing code quicker and easier, and if I look back to my first query, Prompt can qualify those columns for me.
If I press CTRL+B, CTRL+Q, I’ll get this (from the first query).
Note that every column now includes the table names.
It also works for aliases. If I have this (note I’ve added an alias)
CTRL+B, CTRL+Q gives me this:
As I add tables and modify this code, anytime I find columns unqualified, I can use this quick shortcut to fix my code.
Note: If you have ambiguous columns, Prompt can’t fix them (yet).