Names of Tab from SQL Queries

[expired user #6329]'s profile image [expired user #6329] posted 12 years ago in Feature discussion Permalink
Currently the name of the tabs for a SQL query is the name of the table.

I will run 3 - 7 queries all using the same base table for searching. The results is I get multple tabs with the same name on the tab.

Is there a way to have the tabs named something a little more useful?
ansgar's profile image ansgar posted 12 years ago Permalink
The tab caption is set automatically, by analyzing the query. I could add a postfix so they are unique, e.g. "mytable #1", "mytable #2" etc. But is that what you mean by "more useful"?
[expired user #6329]'s profile image [expired user #6329] posted 12 years ago Permalink
My queries are slightly different. For instance, I need to run 3 queries against the Users DB for 3 very different reasons. Seeing Users 3 times isn't useful.

Seeing something other than the table to let me know what it is, is more useful.
Password Check, URL with Email, Banned

Just examples.
ansgar's profile image ansgar posted 12 years ago Permalink
Well, "Password Check" or "URL with Email" is nothing I can name automatically, as you might imagine. That's why I asked if "mytable #x" is more useful for you.
[expired user #6329]'s profile image [expired user #6329] posted 12 years ago Permalink
That's better than thesame name over and over again. :)
[expired user #6302]'s profile image [expired user #6302] posted 12 years ago Permalink
Perhaps the name of the snippet that it's saved into, or the query file you save it into would be more user friendly.....
[expired user #6302]'s profile image [expired user #6302] posted 12 years ago Permalink
any other query and your on your own....smile
[expired user #6302]'s profile image [expired user #6302] posted 12 years ago Permalink
Don't get what you mean, DB Phantom:

"Currently the name of the tabs for a SQL query is the name of the table"

It just shows query#1 result#1 etc... by me!?

i'm messing something?
[expired user #6329]'s profile image [expired user #6329] posted 12 years ago Permalink
I will run several quries at one time. Therefore I will get the table name for the SELECT as the tab name. So if I run several that users is the main table from the SELECT (not counting JOIN tables) I will have several tabs called users.
Code modification/commit 8bc905b from ansgar.becker, 12 years ago, revision 7.0.0.4127
Add postfix to query result tabs so captions are unique. See http://www.heidisql.com/forum.php?t=10493
ansgar's profile image ansgar posted 12 years ago Permalink
r4127 adds a " #2", " #3" etc. postfixes to identical tab captions.
[expired user #6302]'s profile image [expired user #6302] posted 12 years ago Permalink
what happens if you have multiple queries for same table? we're back to same problem, no?

seems the name of snippet/or saved query, would be far more user friendly....clear and adjustable to more scenarios.

my 2 cents anyway...
ansgar's profile image ansgar posted 12 years ago Permalink
> what happens if you have multiple queries for same table?

Please read my above comment - I added the #2, #3 ... postfix for exactly that situation.

The name of the snippet or saved query would not help, as that is the same for all results, isn't it? You do not execute different snippets for each result grids. By the way, we're not talking about query tabs - what DB Phantom means is the tab captions of the result grids in one query tab. One query tab can have multiple result grids, one grid per result, e.g. if you exceute such queries at once:
SELECT * FROM table1;
SELECT * FROM table2;
[expired user #6329]'s profile image [expired user #6329] posted 12 years ago Permalink
OK- just got the nightly build and tried it out. This will work thanks. :)
[expired user #6302]'s profile image [expired user #6302] posted 12 years ago Permalink
Just wondering if this is a good idea...
How about using the commenting text for example:

/* Report A */
SELECT * FROM table1;
/* Report B */
SELECT * FROM table1;

Now each result tab could be named: Report A, Report B.
ansgar's profile image ansgar posted 12 years ago Permalink
You would have to write a comment just to have the tab named after it. Isn't that manual work for the user for a minimal effect?
[expired user #5839]'s profile image [expired user #5839] posted 11 years ago Permalink
Hi. I would actually be very interested in being able to set custom names for these tabs somehow. I have a report that I run that is an SQL file with multiple queries, and it comes up as "Result #1", "Result #2" etc. I do have comments in my SQL so that someone reading it (maintaining etc) knows what each query is doing. It would be nice if we could so something like JavaDoc or PHPDoc where we could put something like this:

/**
* Monthly Sales Query
* 
* @label Monthly Sales
*/
SELECT sum(*) AS total
, month(sell_date) AS month
FROM sales
GROUP BY month;
/**
* Yearly Sales Query
* 
* @label Yearly Sales
*/
SELECT sum(*) AS total
, year(sell_date) AS year
FROM sales
GROUP BY year;


Please don't use my queries as example of good SQlwinkJust indicating how the doc blocks could be used. It would genuinely be useful to me, and I'm sure others too
[expired user #5839]'s profile image [expired user #5839] posted 11 years ago Permalink
Wow, just spotted so many things wrong with those example queries :D
kalvaro's profile image kalvaro posted 11 years ago Permalink
This is a 9 month old thread. HeidiSQL is basically an administration tool and one of its key points is simplicity. (If you want to get lost in your MySQL client you already have Toad for MySQL.)

IMHO, creating a annotation syntax with its corresponding documentation, parser and use cases goes far beyond the abilities that are reasonable to expect from such a tool. I can't think of any other usage apart from running reports and if we want HeidiSQL to be a decent reporting tool it'll lack tons of other basic features.

If you need to run those reports regularly you should invest a few hours writing an application, possibly a PHP-driven web site or even an Excel spreadsheet.
kalvaro's profile image kalvaro posted 11 years ago Permalink
P.S. To be honest, my experience with TOAD is only with their good old Oracle version. It's possible that their MySQL release has a better usability.
[expired user #5839]'s profile image [expired user #5839] posted 11 years ago Permalink
OK, firstly, the age of the thread does not change the fact that this is a feature that I've wanted for a while. Adding a tiny little bit of processing such as this also doesn't add any complexity to the application, if you're perfectly honest. It certainly won't require massive documentation, will it? Also, why would I want to write an application or framework to get data out, when Heidi already does it brilliantly? Quickly knock up a query, export data as CSV (or whatever is required), save the SQL file for later use, done.

Heidi is an excellent tool that I've been using for donkeys, but there's no single feature that makes it a killer app, but rather a collection of MANY small features that just makes it indispensable, IMHO. Being able to label the tabs in a multi-query is just another small feature that I'd love to have. It's hardly a deal-breaker, but it would make it much simpler to read the results. The alternative for me is to have the results contain a field that describes what I'm looking at (so making the beginning of the query SELECT 'YEARLY') for example. That sucks.
[expired user #8875]'s profile image [expired user #8875] posted 9 years ago Permalink
Look! Now it's been two MORE years...

I came here because I was searching for a way to help me differentiate the different tabs.

See attached image example.

I have several Selects and the first one is from "vw_SomeView". Every tab is labeled "vw_SomeView" even though all of the other selects are from other tables.
1 attachment(s):
  • Wut
[expired user #9782]'s profile image [expired user #9782] posted 8 years ago Permalink

I have a similar use case. I paste in a query that has 7 selects. If I can name the tabs then I can easily see what is what. Otherwise I have to check tab position against position of the select command. That's not too hard, but when mentally juggling other things it is a distraction.

I find HeidiSQL a very good tool and this would be a useful addition for me.

dtavres's profile image dtavres posted 8 years ago Permalink

On HeidiSQL Help I see a solution, but my queries aren't as simple as the example.

I'm hoping I'm missing something.

Here's one of my queries: select from(select date(FROM_UNIXTIME(joinStamp-83600)) as date, count(*) from base_user group by 1 order by date DESC LIMIT 20 ) sub order by date ASC;

*I'm a new user, so I can't include the reference - so search google for: heidisql Screenshot: Multiresults

1 attachment(s):
  • multiresults-2-
[expired user #11174]'s profile image [expired user #11174] posted 7 years ago Permalink

I was reading through the comments, hoping to find a solution to the problem of having multiple results tabs with the same name, though I don't see that this has been solved. So I'm commenting to show interest in a solution :)

[expired user #11178]'s profile image [expired user #11178] posted 7 years ago Permalink

joining in - yes, I agree this would be useful. Perhaps the app could continue as is with it's current naming but allow you to say double click a tab and give it a different name if you choose to..

thstolte's profile image thstolte posted 6 years ago Permalink

So I found I way to make this work. instead of doing a query like the following:

select col1, col2 from table1
where col1 is not null;

change it up to be like this:

select col1, col2 from (select col1, col2 from table1) as 'WhateverLabel'
where col1 is not null;

and the tab will show up as WhateverLabel. It's a little redundant, but at least the tabs are labeled what you want.

Hope this helps

qupear's profile image qupear posted 6 years ago Permalink

You would have to write a comment just to have the tab named after it. Isn't that manual work for the user for a minimal effect?

When you do an important updates of data you need to be 100% sure where goes each bit of data and from which tab you take this data. But when there are users#1 users#2 you have to remember what you need to do with #1 and #2. But if you once named your tabs you then can use them later no matter how far thought process gone.

[expired user #12403]'s profile image [expired user #12403] posted 5 years ago Permalink

Or use your select statement to create a temp table named resultset1 and then select * from it afterward, and tab will be named more specific

muzza4's profile image muzza4 posted 5 years ago Permalink

Or use your select statement to create a temp table named resultset1 and then select * from it afterward, and tab will be named more specific

Sure, but that's no good if you want to update a row.

I'd favour a comment directly before the query that HSQL uses to name the tab e.g.

# My Tab 1

select * from etc

If it's not there then use default naming, if it's too long than truncate.

Cheers Muzza

preart's profile image preart posted 4 years ago Permalink

Is there any solution?

ansgar's profile image ansgar posted 4 years ago Permalink

Apart from the above AS somealias workaround - no.

mikepfly2's profile image mikepfly2 posted 4 years ago Permalink

I assume by the "somealias" work around you mean thstolte's post, right? If so, I try that and get an error (see screenshot). I'd love a way to add a comment above the query so I could identity the query result tab name easier.

(Using Version 11.0.0.5919 (64 Bit)) Thank you!

1 attachment(s):
  • 2020-04-30_11-57-49
thstolte's profile image thstolte posted 4 years ago Permalink

I assume by the "somealias" work around you mean thstolte's post, right? If so, I try that and get an error (see screenshot). I'd love a way to add a comment above the query so I could identity the query result tab name easier.

(Using Version 11.0.0.5919 (64 Bit)) Thank you!

I guess the version has been updated so that you don't need the quotes around the label anymore. Try the following and it should work:

select mmcontactid from (select mmcontactid from tblnameContactCSIs) as whateverlabel
where mmcontactid is not null
mikepfly2's profile image mikepfly2 posted 4 years ago Permalink

Unfortunately, no luck.Tab name remains the same:

1 attachment(s):
  • 2020-04-30_12-32-37
thstolte's profile image thstolte posted 4 years ago Permalink

Weird. It worked for me and I'm on the same version.

Description

mikepfly2's profile image mikepfly2 posted 4 years ago Permalink

Hmm. Is it 64 or 32 bit?

qupear's profile image qupear posted 4 years ago Permalink

Weird. It worked for me and I'm on the same version. ?t=10493#p36163

SELECT table_catalog FROM (SELECT table_catalog FROM COLUMNS) AS label WHERE table_catalog IS NOT NULL;

This doesn't worked for me. Version is 11.0.0.5919 64 bit not portable installation.

lieszkol's profile image lieszkol posted 4 years ago Permalink

Yes this would be a nice-to-have. I'd put a bounty up for it if ansgar supported bounties. thstolte's solution worked for me by the way.

Even if the tab was named after the alias of the first table in the query, that would be great. Surely that would not require a large change in the codebase?

So for:

SELECT height, giantswhocanclimb
FROM cliffs AS cliffsofinsanity
WHERE location = 'insanity'

The tab would be labeled "cliffsofinsanity" instead of 'cliffs'?

Just an idea.

Anyways, thanks a gazillion for making HeidiSQL!!!

qupear's profile image qupear posted 4 years ago Permalink

For example in dbeaver naming of resulst tabs works like this:

-- NAME: test

SELECT * FROM table AS t;

mam's profile image mam posted 4 years ago Permalink

I have tried all the ways suggested or described in this thread including using "AS" and none of them work.

mam's profile image mam posted 4 years ago Permalink

Is there going to be a fix for this?

belanp's profile image belanp posted 2 years ago Permalink

I will this really appreciate. I have many queries with several selects included. It will be very easy and fast to add everywhere inside suggested agreement such -- NAME: test

@ansgar, please do it. It will be very helpfull for all of us begging :-). No problems for others.

puppriss's profile image puppriss posted 2 years ago Permalink

OMG Dudes! I've been using HeidiSQL for years (love it), and have just completely accidentally discovered a rather hacky way of naming result tabs. Just precede the query with a comment in the format 'from' <label>. It's ugly, but it's going to make my life slightly jollier - see my little clippet snippet... X

1 attachment(s):
  • Heiditabsnip
qupear's profile image qupear posted 2 years ago Permalink

puppriss looks nice but your hack doesn't work for me. Even with comments like these I still get table name in tab headers.

belanp's profile image belanp posted 2 years ago Permalink

puppriss Good joke, but does not work...

puppriss's profile image puppriss posted 2 years ago Permalink

qupear Ah, that's a shame. I'm on Win10 64 Heidi v.12.2.0.6576. Since I posted, I have noticed that it doesn't work for me 100% of the time. But I assure you belanp that my screenshot is absolutely genuine and worked as shown

alex777's profile image alex777 posted 2 years ago Permalink

I've been able to get distinct (but not custom) tab names by using a semicolon after each statement. For instance:

select top(5) * from table1;
select top(5) * from table2;
select top(5) * from table3;

See also the attachment

1 attachment(s):
  • heidisql
belanp's profile image belanp posted 2 years ago Permalink

This encapsulation should work as work arround: SELECT × FROM (SELECT columns FROM table1, table2 WHERE conditions) AS TAB_NAME

However some new "command" used in comments will be heplful and harmless for all.

preart's profile image preart posted 2 years ago Permalink

'OMG Dudes! I've been using HeidiSQL for years (love it), and have just completely accidentally discovered a rather hacky way of naming result tabs. Just precede the query with a comment in the format 'from' <label>. It's ugly, but it's going to make my life slightly jollier - see my little clippet snippet... X'

This only works if UNION is present in SQL :-/

UweAtWork's profile image UweAtWork posted 6 months ago Permalink

It would be great if we could change the name of the TAB in the query-windows programmatically.

I tried this in order to force the name 'TO DO' - but it does not work - it still shows 'Result #1':

-- NAME: TO_DO
SELECT -- TO_DO
    TO_DO.TO_DO
FROM (SELECT 'Please undo...' AS TO_DO) TO_DO;
ansgar's profile image ansgar posted 6 months ago Permalink

Next build parses such a "-- name: xyz" comment over the actual query. That comment does not necessarily need to be the first line. Note if present then it takes precedence over a table name:

Description

Code modification/commit 9364da6 from Ansgar Becker <anse@heidisql.com>, 6 months ago, revision 12.6.0.6820
Support naming result tabs per "-- name: xyz" comment. See https://www.heidisql.com/forum.php?t=10493
puppriss's profile image puppriss posted 6 months ago Permalink

Next build parses such a "-- name: xyz" comment over the actual query. That comment does not necessarily need to be the first line. Note if present then it takes precedence over a table name:

Supergreat! Thnx

belanp's profile image belanp posted 6 months ago Permalink

Next build parses such a "-- name: xyz" comment over the actual query. That comment does not necessarily need to be the first line. Note if present then it takes precedence over a table name:

Description

EXCELLENT THANKS !!!

carloscastaneda's profile image carloscastaneda posted 6 months ago Permalink

I have just tried this nice feature. In simple cases it works well, but as soon as the queries become more complicated, the tab name becomes very wide. Here some queries to test: test1 works fine, 2

-- NAME: test 1
SELECT table_schema, table_name FROM information_schema.tables a LIMIT 1;

-- name: test2
SELECT table_schema, table_name FROM information_schema.tables a 
WHERE EXISTS(SELECT 1) LIMIT 1;

-- name: test3
SELECT table_schema, table_name FROM information_schema.tables a
WHERE EXISTS(SELECT * FROM information_schema.columns b 
    WHERE a.TABLE_NAME=b.table_name AND a.table_schema=b.table_schema) LIMIT 1;

-- NAME: test 4
SELECT b.table_schema, b.TABLE_NAME, b.column_name
 FROM information_schema.tables a INNER JOIN information_schema.columns b
  on a.TABLE_NAME=b.table_name AND a.table_schema=b.table_schema
LIMIT 1;

Here is the result of running all queries in a single batch. Tab test 4 cannot be seen! Description

queries test 4 and test 1 Description

1 attachment(s):
  • test2
ansgar's profile image ansgar posted 6 months ago Permalink

Should not happen, even if there are spaces behind the "name: test2". HeidiSQL trims the result name which should remove spaces.

Anyway, could you please check whether you have trailing spaces in the comment?

carloscastaneda's profile image carloscastaneda posted 6 months ago Permalink

Should not happen, even if there are spaces behind the "name: test2". HeidiSQL trims the result name which should remove spaces.

Anyway, could you please check whether you have trailing spaces in the comment?

I checked the statements, there are no trailing spaces. The repo in my previous note is the very same.

Code modification/commit dec0ace from Ansgar Becker <anse@heidisql.com>, 6 months ago, revision 12.6.0.6822
Fix too greedy expression for finding result name. See https://www.heidisql.com/forum.php?t=10493
ansgar's profile image ansgar posted 6 months ago Permalink

Confirmed using your samle code.

Problem was the regular expression used for the result name, taking several new lines into the name.

Fixed in the next build:

Description

carloscastaneda's profile image carloscastaneda posted 6 months ago Permalink

It works in the new build 6623 🙏

belanp's profile image belanp posted 6 months ago Permalink

There is a problem when you use variables in DECLATE. Then SEMICOLON ";"can not be used and then all tabs have the same "FIRST" name. Description

belanp's profile image belanp posted 5 months ago Permalink

Most of our scripts are using DECLARE statment, because we need more actions on the same data. Please fix this problem described above with SEMICOLON.

ansgar's profile image ansgar posted 5 months ago Permalink

@belanp please post the code you have and which leads to confusion here. Your screenshot only shows the result tabs, and the code is hidden.

belanp's profile image belanp posted 5 months ago Permalink

@belanp please post the code you have and which leads to confusion here. Your screenshot only shows the result tabs, and the code is hidden.

DECLARE @x integer

-- name: Result 1 SET @x = 1 SELECT 1 WHERE 1= @x

-- name: Result 2 SET @x = 2 SELECT 2 WHERE 2=@x

belanp's profile image belanp posted 5 months ago Permalink

DECLARE @x integer

-- name: Result 1

SET @x = 1

SELECT 1 WHERE 1= @x

-- name: Result 2

SET @x = 2

SELECT 2 WHERE 2=@x

ansgar's profile image ansgar posted 5 months ago Permalink

@belanp You need to separate the queries with a semicolon, at least after the SELECT, so the "name:xyz" is bound to the query inside:

DECLARE @x INTEGER

-- name: Result 1
SET @x = 1
SELECT 1 WHERE 1= @x;

-- name: Result 2
SET @x = 2
SELECT 2 WHERE 2=@x;
UweAtWork's profile image UweAtWork posted 5 months ago Permalink

With HeidiSQL build 12.6.0.6837, connected to MariaDB Galera Cluster 10.6.17, this does not seem to work from within stored-procedures. Should this work or is it impossible to implement?

ansgar's profile image ansgar posted 5 months ago Permalink

No, not within a procedure definition. Only outside, in a query tab.

ferino's profile image ferino posted 5 months ago Permalink

@belanp You need to separate the queries with a semicolon, at least after the SELECT, so the "name:xyz" is bound to the query inside:

DECLARE @x INTEGER

-- name: Result 1
SET @x = 1
SELECT 1 WHERE 1= @x;

-- name: Result 2
SET @x = 2
SELECT 2 WHERE 2=@x;

sorry, but not working: / SQL error (137): Must declare the scalar variable "@x". /

ansgar's profile image ansgar posted 5 months ago Permalink

@ferino you are mixing things up here. Be aware these "-- name: xyz" comments are supported only in the query editor. Nowhere else. Please test with your own queries. The SQL above was just an example taken out of your own comment.

belanp's profile image belanp posted 5 months ago Permalink

@belanp You need to separate the queries with a semicolon, at least after the SELECT, so the "name:xyz" is bound to the query inside:

It is not working on MS SQL AZURE. If you use ";" semicolon there, then variable is unknown in second select a produce ERROR. Could you please use for parsing only comment "-- name: XYZ". No semicolon is needed there for parsing...

preart's profile image preart posted 5 months ago Permalink

It doesn't work for me either:

-- name: name1
SELECT 1 AS n1
;
-- name: name2
SELECT 2 AS n2 
;
1 attachment(s):
  • Screenshot_1
ansgar's profile image ansgar posted 5 months ago Permalink

Please note the semicolon is required for splitting queries, and for finding the right tab name from the "name" comment.

Also, you need to activate "Send queries one by one" for it to work properly:

Description

ferino's profile image ferino posted 5 months ago Permalink

On the mysql server (5.5.5-10.5.9-MariaDB-1:10.5.9+maria~focal-log) it's different again: image description "Send queries one by one" is activated.

1 attachment(s):
  • semicolon
ferino's profile image ferino posted 5 months ago Permalink

So I don't know where it actually works.

ansgar's profile image ansgar posted 5 months ago Permalink

As said, the semicolon ist the crux. I tried to make it more clear with some color applied on the code areas which you splitted with a semicolon. Your tab names are applied on the SET.. queries only, which do not create a result:

Description

Elon Musk's profile image Elon Musk posted 5 months ago Permalink

at last, 2024 people is here

VFRDavid's profile image VFRDavid posted 3 months ago Permalink

How about using the table alias (for example FROM table_name AS table_alias ) for the name of the query's tab? I am only getting "Result #1 ... Result #2...etc - which doesn't even use the name of the primary (or in my case, only) table I'm selecting from. I attached a screen shot showing this along with the overly simple bit of code I use that checks the call_log table to see how recent the data is there (since these are supposed to be real-enough-time mirror servers - but sometimes lag - and I like to check before running anything time-sensitive against them). Like a previous poster, I have been doing this positionally - remembering which SELECT statement came in what order - which isn't that difficult - but - would definitely be easier if they were named from the name of the FROM table's alias...that way we would be in FULL control of the tab's name...thanks!

1 attachment(s):
  • Heidi
VFRDavid's profile image VFRDavid posted 3 months ago Permalink

Please ignore my last reply about the "...FROM table_name AS table_alias..." - the -- name: TabName works beautifully!

THANK YOU!!!

VFRDavid's profile image VFRDavid posted 3 months ago Permalink

Example of multiple tabs being named using the -- name: TabName syntax - thanks Ansgar!

Description

belanp's profile image belanp posted 3 months ago Permalink

Semicolon "destroys" declaration of the variable, but we all use the same value of variable on several selects in the same script. So we cannot use semicolon. We need to set variable value only once at the top of script, instead of 10 times, because we always change this value manualy, before we run this script.

When we do not use semicolon Heidy stil gives us different tabs for each (main) select. But we are not able to give them meaningful names.

Just collect "List of tab names" of all tabs defined TAB names in script. Then you generate TABs with results in Heidy frontend. And then give them names from the "List of TAB names" - one by one. It's easy. Furthermore we can define the "List of names" anytime, for example at the begin of the script, if it is problem to collect them during parsing rows. Like: -- list of tab names: "aplles", "berries", "melons" We just need the way how to give names for the TABS in the comments. Nothing more. Thanks a lot for you work with Heidy.

ansgar's profile image ansgar posted 3 months ago Permalink

Looks like a nice extension to that "-- name:" comment.

Next build supports a new "-- names:" comment, and please note the plural "s":

  • will be splitted by comma
  • will be used to populate the result tab captions, as much as it holds
  • -- names: xyz, abc, ... can be placed in the first KB of SQL text
  • it may be part of the first query, but does not need to. It can also appear in the second or later queries.
  • -- name: xyz has still priority, when also used for a single query

Here's an example, showing some special cases:

Description

belanp's profile image belanp posted 3 months ago Permalink

Great, WORKS EXCELENTLY, thanks :-)

Please login to leave a reply, or register at first.