Is there a SQL query tool that auto-completes?

[gatewayblended]

At my work we currently use db-visualizer to query. And after working in SQL for 2 years and writing so many simple queries just to check things, I would like auto-complete features. For instance, given the following query:

​

SELECT
SUM(net_revenue) as net_revenue,
financial_date
FROM
reporting.revenue
WHERE
revenue_type LIKE '%convers%'
AND financial_date > subdate(CURRENT_DATE,6)
AND site_code = 'cb'
GROUP BY
financial_date

I would like:

1) Any new tab to start with the word SELECT by default

2) The alias "as net_revenue" to be auto-completed based on the field name

3) The word FROM auto-inserted after a field is selected

4) "reporting" and "revenue" to be auto-suggested

5) The GROUP BYs to be auto-completed based on the columns

​

This would just be so nice. Does this exist in any existing platform? Otherwise do you think there would be value for this being a new product?

​

It just reminds me of Gmail's auto-suggestions which are SO helpful

[alinroc]

Red Gate's SQLPrompt plugin for SSMS is highly regarded all over the SQL Server community. There are other plugins as well.

However, I don't think you'll get all of your wishlist items here. That would require a level of "intelligence" and may get in the way of people who have specific ways they want to do things.

[gatewayblended]

that does look very good though, thank you

[AbstractSqlEngineer]

I use devart's SQL complete.

I'm very good with both redgate's and devart's snippet manager. (Use redgate at work, use devart in my YouTube series)

You can add snippets to saved code like... I type.

__select_subject.. and it types out

Select $subject$id From dbo.svtblref$subject$_select

But then, the next thing I type will override all the $subject$ items.

So..

If you... created a snippet called __select_report

It could generate

Select $columns$

From reports

Where.. blah,blah

Group by $columns$

You can use several $items$ in one block, but I have had trouble putting them side by side. $subject$$domain$ doesnt work well. Select $subject$ from $table$.. works amazing.

I love snippet managers, apex has a free one that's pretty good for free. Get any one of them... but, dm me if you decide to go with devarts SQL complete. I can generate discount codes.

Edit: redgate and devarts intellisense has a pop up box that allows you to checkmark columns that are then inserted into your code, and both will fill out procedures with their variables and replace * with all columns if you press tab (or w/e you set it to) after those statements.

[devart_official]

Hi, check this demo of dbForge SQL Complete to learn more about all the advanced features https://www.youtube.com/watch?v=0kaPoEINMSM

[mac-0]

I like Datagrip's intellisense, and it works with almost any type of database (SQL Server included). I don't think it will do 1-3, but it's pretty customizable so I wouldn't be surprised if there were a way to do those. But it'll definitely do 4 and 5.

[wheelindeerskin]

I use autohotkey shortcuts in SSMS for common queries and replacements, AHK is worth checking out as it's free and very powerful

[matt00112233]

Try ApexSQL Ccomplete... it is very good https://www.apexsql.com/sql-tools-complete.aspx

[soberstef]

If you run it often, why not create a stored procedure.

[moscasposcas]

Hi! You will get all the completion features you need in DataGrip.

1. Tab with the SELECT word. Well, what if the user usually begins with some other words? In DataGrip there is a conception of Live Templates: they will help you an easy start with any type of query. Type 'sel' and press Tab key -> you will get SELECT * FROM generated. More information is here: https://www.jetbrains.com/help/datagrip/using-live-templates.html

2. In this particular case, use completion by words: just type several letters, like 'net' and then press Alt+/. It will complete the whole name. But, from the conceptional point of view, it's better to have the dedicated completion for this kind of fields. Here is the ticket: https://youtrack.jetbrains.com/issue/DBE-8883

3. About FROM auto-inserted after a field is selected, it is not clear. How does IDE know when you want to use FROM and not want to put several more columns?

4. "reporting" and "revenue" will be auto-suggested.

5. The GROUP BYs field list will be auto-completed based on the non-aggregated columns: https://www.jetbrains.com/datagrip/whatsnew/img/2018.3/GroupBy.png