26 January 2017
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.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly in SQL Server Management Studio and Visual Studio.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    SQL Prompt Code Analysis: Table does not have clustered index (BP021)

    With a few exceptions, every table should have a clustered index. However, they are not always essential for performance. The value of a clustered index depends on the way a table is used, the typical pattern of queries, and how it the table is updated. More important for a table is that it should have

  • Article

    How to format a set of SQL scripts using SQL Prompt

    Any database developer or DBA who spends much of their working week staring at SQL code quickly becomes set in their ways. They like to see the code laid out in a very particular way. They will struggle to look at, let alone digest and understand, code formatted in a ‘foreign’ style. It’s also rare

  • Article

    Quick SQL Prompt tip - picking the columns you need for speedy results

    One of the poor practices that I see so many people doing in code is using SELECT *. This is poor practice because it takes more time to complete for clients, it’s an unnecessary use of resources on the client, server and network, and it can fill the buffer cache with rarely-used data. For example,

  • Article

    Using multiple custom SQL formatting styles in SQL Prompt

    Usually, when coding SQL, you will want to go along with the coding style of the team you are working with. In my previous articles on SQL Prompt, I explained why a team of programmers might want to adopt a T-SQL coding standard, how SQL Prompt could make this easier, and then how to use

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly