26 January 2017

Quick SQL Prompt tip – script objects as ALTER in two clicks

Working in a large database can be difficult at times. While many of us might learn the meanings and definitions of most objects, it’s easy to forget the exact ways in which some objects work, or what the behavior is in certain calls. This is one place where having tools that assist you like SQL Prompt can make you a much more efficient developer.

Let’s say I run into a query like this one:

SQL Prompt tip 1

There’s a good naming convention here, but am I sure of exactly how the function works? Can a parameter change things? Is this a scalar function that might cause performance issues? All of those are good questions, and I want to know the answers.

SQL Prompt can help here. If I right click on the function name, I get a context-sensitive menu. In the middle, I have some specific items added by Prompt:

SQL Prompt tip 2

I’ve selected one of these, the Script Object as ALTER item. When I choose this, I get a new query window opening with the code for this object:

SQL Prompt tip 3

This is titled with the object name and has the code that allows me to change the object, if I desire. In this case, I can see this is a simple date manipulation that returns the current accounting ending date for a hard-coded date. Fine for the Microsoft AdventureWorks database, but probably not the kind of code I want.

At this point I can change the code, or go back to my query and see if this makes sense. As I encounter other objects, rather than browse through the object explorer or use Search, I can quickly use Script Object as Alter to get the underlying code and proceed with my development.

This is another way in which SQL Prompt can improve your coding efficiency and habits with a little practice. I’d encourage you to try using this feature the next time you’re drilling down into an object in your database.

I cover how to use SQL Prompt to script out objects as an ALTER statement and more in the new series of #SuperSQLTips videos. Take a look and you’ll find handy tricks from other Data Platform MVPs and SQL Server experts to help you write SQL more quickly and accurately:

If you’re not a SQL Prompt user, download a free trial and see how much more productive you can be writing code every day.

Like this? Share it with other SQL professionals using the social sharing buttons below. Simply tweet: I’ve just read @way0utwest’s blog on how to script objects as alter with #SQLPrompt – see the #SuperSQLTip at http://rd.gt/2jUYb77.