Applies to
Summary
This article explains how to use ApexSQL Complete to script out objects as an ALTER statement in a few clicks using the Alter object scripting feature. Furthermore, it shows the resemblance to the Show object script feature that helps users with viewing the object definition and description directly in the query editor.
Description
An option for scripting SQL Server database objects can be useful for reviewing the exact way in which some objects work. Similarly, having the ability to review an object’s definition at your fingertips can speed things up when troubleshooting a problem especially on large databases.
Let’s head over to SQL Server Management Studio and see how ApexSQL Complete can improve SQL coding and productivity. First, we will take a look at how to view an object’s script and description, and then we will move on to scripting objects for altering in two clicks.
View the definition of objects
One way to view the definition of an object in the query editor is to enable the Show object script option from add-in settings.
Go to the ApexSQL main menu (can be found under the Extension menu in VS 2019), navigate to ApexSQL Complete, and choose Options. Under the General tab, check the Show object script option and hit OK to save changes and exit the window:
Now, open a new query and start typing e.g. a simple EXECUTE statement. To view the definition of a stored procedure in the query editor, select the procedure from the hint-list of suggestions and an additional window will appear to the right displaying the object’s definition as shown below:
|
Quick tip: If no content-sensitive hints are shown for user-defined stored procedures and functions go to the add-in Options window, change the tab to Hints on the left, and make sure that the User-defined stored procedures and functions option is checked. Refresh cache is necessary for the already opened query editors for the change to take effect |
Within the same window, switch over to the Description tab to see extended properties, if available:
Now, this code preview window is good enough in most cases, but what about when we’re dealing with large scripts? For example, in the script below, it’s hard to read the rest of the code and the user must scroll up and down to see the full script:
This is the case when an option to quickly view the entire code comes in handy. Let’s see how to do this in the next section of the article.
Alter object scripting
One way to view the entire script is to locate the object in Object Explorer, right-click it and then click the appropriate option to script it out. This can be a time-consuming task with more than just a few clicks, especially if you take the time needed to find the object in Object Explorer just to display the object definition.
However, using ApexSQL Complete users can script out an object from the query editor directly. Here is how it works using the same stored procedure from the previous example.
Right-click the object you want to script out, and from the context menu, click Alter object scripting as shown below:
This will script out the object in a new query editor as an ALTER statement:
From here, users can look at and review code, change some parts of the code if needed, save it quickly, go back to the original query editor, etc.
Alter object scripting feature can script out: functions, stored procedures, triggers, and views. If the command is used on an unsupported object, the following message will be shown:
