Worked Examples |
See Also |
This topic shows you how you can use SQL Prompt. The following examples are provided:
You can follow the examples on your own system. Most examples in this topic use SQL Server 2005 and you will need access to a SQL Server on which the AdventureWorks example database has been installed. In addition, the cross-database queries and queries using linked SQL Servers examples use SQL Server 2000 on which the Northwind and pubs example databases have been installed.
Before you follow the examples, to ensure that you see the same results as those illustrated, set your options to the default values. To do this on the SQL Prompt menu, click Options, and then click Restore All Defaults.
This example shows you how you can use the SQL Prompt candidate list to assist you when you are typing a query.
SELECT * FROM
and press SPACEBAR.
The candidate list displays the Suggested candidates category; tables are displayed at the top of the list.
The Contact table is highlighted.
SQL Prompt inserts the table name. Because you selected the Qualify object names option, SQL Prompt qualifies the table name with the schema (owner) name Person. The identifiers are surrounded with brackets [ ]; this is another option in Inserted Candidates, Formatting, which you can switch off if required.
The candidate list displays column names for the Contact table and associated data types.
By default, when you type an opening quotation mark, SQL Prompt automatically inserts the closing quotation mark. If you type the closing quotation mark (for example, because you have not noticed the automatic insertion), SQL Prompt overtypes the auto-inserted quotation mark. You can change this setting and other auto-completion options, on the Auto Insert, Closing Characters options page.
The candidate list displays columns from the Contact table.
Note that you must always type * after the SELECT keyword, even when you want use SQL Prompt to insert the column list later. If you do not type * the candidate list may display incorrect candidates. For more information, see Troubleshooting
These examples show you how SQL Prompt can expand a column list when you are using a SELECT statement.
In the first example, you type SELECT * and you specify the tables or views in the FROM clause; you can then complete the column list by placing the insertion point after the * and pressing TAB:
SELECT * FROM [Person].[Contact]
SQL Prompt expands the column list.
This next example shows how SQL Prompt can complete the column list for you when you type a SELECT table.* fragment and press TAB.
SELECT [Person].[Contact].*
SQL Prompt expands the column list. This time, SQL Prompt qualifies the columns with the table name and schema (owner) name irrespective of your Qualify column names option setting, because you specified the table name.
This example shows how you can use the SQL Prompt column picker to select columns for the column list in a SELECT statement:
SELECT * FROM [Person].[Address] INNER JOIN [Person].[AddressType]
The candidate list displays columns from the Address table and the AddressType table.
To select a column, click the check box, or highlight the column and press SPACEBAR. You can use SHIFT and CTRL to select multiple columns.
For more information about how to use the column picker, see Using the Candidate List.
These examples show how SQL Prompt generates full INSERT statements.
SQL Prompt inserts the INSERT statement. The insertion point is positioned for you to enter the values.
In the following example, hints are not shown.
SQL Prompt inserts the INSERT statement. The insertion point is positioned for you to enter the values.
In the following example, default values are not shown for data types.
SQL Prompt inserts the INSERT statement. The insertion point is positioned for you to enter the values.
This example shows how SQL Prompt inserts the parameters for stored procedures when you type EXEC and you select a stored procedure.
SQL Prompt displays a list of stored procedures at the top of the candidate list. The schema panel shows the creation SQL for the highlighted stored procedure. For example:
SQL Prompt inserts the parameters for the stored procedure and inserts the associated data types as comments. For example:
SQL Prompt is pre-configured with default snippets. To see the snippets, press CTRL+SPACEBAR and then CTRL+LEFT ARROW to display the Snippets category.
This example shows you how to insert the snippet code for disabling all triggers on a table.
atdta
The candidate list displays a description of the snippet and the schema panel displays the snippet code.
SQL Prompt places the insertion point after TABLE for you to type the table name.
These examples use SQL Server 2000 on which the Northwind and pubs example databases have been installed.
The first example begins using the pubs database, and then accesses a table in the Northwind database.
USE [pubs] GO SELECT * FROM Nor
The candidate list displays the candidates. For example:
SQL Prompt caches the Northwind database, and displays a list of the owners in the database. (Note that you may be asked to enter authentication credentials if SQL Prompt has been unable to log on to the database.)
SQL Prompt displays the candidate list containing tables and views.
SQL Prompt inserts the columns from the dbo.Products table in the Northwind database.
This next example shows a distributed query using a linked SQL Server 2000 instance from a SQL Server 2005 instance. The linked SQL Server 2000 instance is configured with the name READONLY.TESTNET on the SQL Server 2005 instance. For more information about linking servers, refer to SQL Server Books Online.
USE [AdventureWorks] GO SELECT * FROM rea
The candidate list displays the candidates. For example:
If you have not saved the connection details, SQL Prompt displays a dialog box for you to enter the authentication credentials.
If you click Do Not Connect, in future SQL Prompt will not display the candidate list for that SQL Server automatically; if you want to enter the credentials at a later date so that you can see candidates, press CTRL+SPACEBAR.
For this example, enter the credentials and click Authenticate, and then press CTRL+SPACEBAR to display the candidate list. The candidate list shows a list of databases on the linked server.
You may need to press CTRL+SPACEBAR to display the candidate list. If the authentication credentials you entered allow you unrestricted access to the SQL Server, you are not prompted to enter them again.
SQL Prompt inserts the columns from the dbo.Customers table in the Northwind database.
See Also |
Using SQL Prompt | Using the Candidate List | Using the Schema Panel | Managing Aliases | Managing Snippets | Managing Connections | Setting Options | Tips | Troubleshooting
© Red Gate Software Ltd 2007. All Rights Reserved.