Editing Database Views and Stored Procedures

“Ninety percent of the time you just need to make a simple modification,” the Redmond Magazine reviewer wrote in the introduction to the recent review that selected DatabaseSpy as Redmond Roundup Champion among database tools.

DatabaseSpy lets you make those quick updates with its intuitive interface that is consistent across multiple database types.

For the other ten percent of the time, DatabaseSpy can also be an appropriate tool for more advanced database maintenance tasks, such as creating or altering database views and stored procedures.

Let’s take a look . . .

The DatabaseSpy 2008 Online Browser lets you explore views and procedures by navigating and expanding them the same way you can explore tables in the hierarchical display of the Online Browser helper window.

Edit Database Views

When you select an existing database view in the Online Browser window, the right-click context menu offers options to generate new statements in the SQL Editor that can quickly get you started editing a view, or to assist creating a new one.

For instance, when you generate a create statement for an existing view, DatabaseSpy displays the formatted and color-coded view definition for immediate access in a new SQL Editor window.

Create a New Database View

You can modify the statement any way you like in the SQL Editor. As an example, let’s assume you’ve been assigned to create a company phone directory view. All the data you need is already contained in the employees view – plus a lot more! You can start by saving a copy of the employee view with a new name. Simply edit the database view name, execute your statement, and the new database view is created.

As you refresh the database connection in the Online Browser window, the new view is immediately visible and available for access.

Alter a Database View

You can choose the Alter selection from the right-click context menu to edit any database view, including the new phone directory view.

You can revise the existing view to make the changes you want, taking advantage of all the DatabaseSpy SQL Editor functionality, including color coding, automatic formatting, and even auto-completion.

When your edits are complete, the SQL Editor Execute button runs the alter statement and modifies the view in the database.

To make access to the new phone directory view even easier for the HR department, you can save a select statement for the view in a SQL file and add it to the HR manager’s DatabaseSpy Project menu.

Use SQL Refactoring to Create a View

If you don’t have an existing view to use as a template, DatabaseSpy 2008 offers a convenient alternative. The SQL Refactoring menu includes an option to convert any select query to a create view statement. The default view name is even highlighted so you can immediately assign a more relevant name.

Edit Stored Procedures

The DatabaseSpy 2008 Online Browser also lets you edit stored procedures in your database. You can highlight any stored procedure, then can expand your selection to explore it. Or, use the right-click context menu to generate SQL statements for operations available to act on stored procedures.

You can edit stored procedures in the DatabaseSpy SQL Editor, and execute your revised statements using the same techniques described above for database views.

Execute Stored Procedures

The Execute option in the DatabaseSpy 2008 context menu for stored procedures builds a time-saving template for an execution script for the stored procedure.

For procedures that require parameters, all you have to do is set the parameter values, then it’s just one click to execute the stored procedure and view the results.

Edit User-defined Functions

DatabaseSpy 2008 even lets you navigate and edit user-defined functions stored in your database, starting from the same convenient right-click context menu in the Online Browser.

Try this for yourself with a free trial of Altova DatabaseSpy.