ProductsDatabaseSpyEdit Views and Stored Procedures

The DatabaseSpy® 2009 Online Browser and SQL Editor combine to provide a powerful toolset for editing database views and stored procedures.

The DatabaseSpy® 2009 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® 2009 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.

Click image to enlarge screenshot

Create a New Database View

If you don’t have an existing view to use as a template, DatabaseSpy® 2009 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.

Click image to enlarge screenshot

Edit Stored Procedures

The DatabaseSpy® 2009 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.

Click image to enlarge screenshot

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® 2009 context menu for stored procedures builds a time-saving template for an execution script for the stored procedure.

Click image to enlarge screenshot

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.

Click image to enlarge screenshot

Edit User-defined Functions

DatabaseSpy® 2009 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.

Click image to enlarge screenshot

Whenever your database development and maintenance tasks require editing database views, stored procedures, or user-defined functions, DatabaseSpy® 2009 offers all the convenient and efficient tools you need.

Try award-winning DatabaseSpy® 2009 for yourself. Download a free 30-day trial!