Is there a way to generate SELECTs with table aliases?

Hi,

Can I configure the Copy SQL Code as/SELECT statement/SQL to add ALIASES to the table, and precede each generated field with the alias?

E.g., for CUSTOMER the SELECT might be

	Code:
	SELECT a.account_number, a.customer_name, a..... FROM accounts.customer a

I'm sorry, I overlooked your post yesterday.

The context menu based shortcut in DB Explorer Copy SQL Code As -> SELECT -> SQL generates SELECT query for a table, the same query it would use internally for quick data preview or data export, and it then just copies that internal query to the editor. Unfortunately you cannot customize that internal SELECT query. If the SQL Intellisense isn't enough for what you want,
here is an alternative method that you may find usable because it enables some degree of customization using code snippets.

So, create a code snippet with the following code

	Code:
	$$ SELECT line_text FROM ( SELECT 0 AS ordinal_position, 'SELECT' AS line_text UNION ALL SELECT DISTINCT ordinal_position, CASE WHEN ordinal_position = 1 THEN ' ' ELSE ' ,' END + 'a.' + column_name AS line_text FROM information_schema.columns WHERE table_name = '$CURRENT(word)$' UNION ALL SELECT 9999 AS ordinal_position, 'FROM $CURRENT(word)$ a' AS line_text ) t ORDER BY ordinal_position $$

Note use of "+" or "||" in the above code depends on the database type you work with.

In the code snippet properties choose to insert the output into code (that's editor) and also choose formatting style you want to apply to the code before it gets inserted.

Now, to use that snippet, drag-and-drop table name from the database explorer on to the editor. Right after that press Ctrl+Shift+Space to invoke the command palette. Click your new code snippet in the list (look for command beginning with "SELECT line_text"). And viola, you got the code generated. Hint, to make that snippet always visible without scrolling, resize the command palette window, it will remember its size next time you use it.

While you can achieve similar result using built-in SQL Intellisense with the about the same amount of clicks and key presses, here with the code snippet you have the flexibility to customize output more to your liking adding additional conditions, your own way of choosing aliases, etc... basically adding more code logic to the snippet

I'll give this a try, thanks.

SysOp wrote:

I'm sorry, I overlooked your post yesterday.

The context menu based shortcut in DB Explorer Copy SQL Code As -> SELECT -> SQL generates SELECT query for a table, the same query it would use internally for quick data preview or data export, and it then just copies that internal query to the editor. Unfortunately you cannot customize that internal SELECT query. If the SQL Intellisense isn't enough for what you want,
here is an alternative method that you may find usable because it enables some degree of customization using code snippets.

So, create a code snippet with the following code

	Code:
	$$ SELECT line_text FROM ( SELECT 0 AS ordinal_position, 'SELECT' AS line_text UNION ALL SELECT DISTINCT ordinal_position, CASE WHEN ordinal_position = 1 THEN ' ' ELSE ' ,' END + 'a.' + column_name AS line_text FROM information_schema.columns WHERE table_name = '$CURRENT(word)$' UNION ALL SELECT 9999 AS ordinal_position, 'FROM $CURRENT(word)$ a' AS line_text ) t ORDER BY ordinal_position $$

Note use of "+" or "||" in the above code depends on the database type you work with.

In the code snippet properties choose to insert the output into code (that's editor) and also choose formatting style you want to apply to the code before it gets inserted.

Now, to use that snippet, drag-and-drop table name from the database explorer on to the editor. Right after that press Ctrl+Shift+Space to invoke the command palette. Click your new code snippet in the list (look for command beginning with "SELECT line_text"). And viola, you got the code generated. Hint, to make that snippet always visible without scrolling, resize the command palette window, it will remember its size next time you use it.

While you can achieve similar result using built-in SQL Intellisense with the about the same amount of clicks and key presses, here with the code snippet you have the flexibility to customize output more to your liking adding additional conditions, your own way of choosing aliases, etc... basically adding more code logic to the snippet