As a follow-up of the post String Literals as Column Aliases are Deprecated, this one feature from Redgate’s SQL Prompt is quite handy to correct any existing SQL scripts quickly.
Using the SQL from How to Find All References to an Object in a SQL Server Database as an example, here’s what we originally have:
SELECT DISTINCT OBJECT_NAME(m.object_id) AS 'object name', m.* FROM sys.sql_modules m WHERE m.definition LIKE '%Customer_Name%'
Pay attention to the object name within the single quotes as a column alias.
If this is only a one-off such as this one, it’s no sweat to fix it manually, but what happens if you have a long query with multiple lines of column aliases that you want to fix?
If you have Redgate SQL Prompt, you just need to set it once in the Options as shown below:
In
In the Options window, go to Format, Styles. Under Actions, check Apply column alias style and select column AS “alias”, then click OK.
You’re done with the configuration.
How do you use it?
The best way to use it is by using a keyboard shortcut.
If you click on the SQL Prompt from the menu again, you’ll see an option for Format SQL with a shortcut CTRL+K, CTRL+Y.
In your query window where you have the SQL you want to fix, just use the keyboard shortcut (or you can do that from the menu as well) and right away, it will reformat the query with the fix:
SELECT DISTINCT
OBJECT_NAME(m.object_id) AS "object name",
m.*
FROM sys.sql_modules m
WHERE m.definition LIKE '%Customer_Name%';
This is one of the useful features in SQL Prompt to make your life easier. If you notice from the screenshots above, the top bar also has a red color to indicate that it is a production database. It’s a nice way to color code different database environment that can also be configured in SQL Prompt.
Further Reading
How to Use Custom Color in SSMS Using Redgate SQL Prompt
Download
Redgate SQL Prompt (FREE Trial)
