dbvisualizer: set max rows in a select query

Question

I am using DBVisualizer 8.0.6 and when I run a simply query like....

select * from table

It only shows the first 1000 rows and then stops the query and displays in the bottom left corner... "Number of rows limited by maxrows"

How do I change this #? I'm writing a query which needs to export a little over 1000 rows but dbvisualizer has this set limit...

I tried something like @set maxrows 2000 then commit then run my query. Still returns only 1000 rows. This is for an Oracle table.

score 66 · Accepted Answer · answered Dec 11 '11 at 21:16

66

There is a box in SQL Commander labeled Max Rows. Set it to -1 for the complete result set.

Max Rows

answered Dec 11 '11 at 21:16

James Allman

40,573
11
57
70

4

OH WIN lol, it was right in front of my face and I never saw it :) – HelloWorld Dec 12 '11 at 23:04
2

many thanks, i went to the dbvisualizer docs but couldn't find this info quick enough, only this page was able to simply answer this. – Carlos Jaime C. De Leon Feb 06 '12 at 06:19

score 4 · Answer 2 · answered Feb 07 '12 at 10:35

Or you could just export directly to a file. This will allow to export many more rows than the DBVisualizer GUI can show you. When having to export a few million records (should you ever need that), this is quite useful.

Simply do something like this in your SQL Commander:

@export on;
@export set Filename="d:\temp\export" format="CSV" DecimalNumberFormat="00000000000" CsvRowDelimiter="\r\n" CsvIncludeColumnHeader="false";

SELECT YOURFIELD FROM YOURTABLE WHERE SOMEFIELD = AFILTERVALUE;

You can find more about this (and the various parameters) here: http://www.dbvis.com/products/dbvis/doc/7.1/doc/ug/sqlCommander/sqlCommander.html#mozTocId448386

When doing @export, it still only exports the number of rows specified by the GUI Max Rows setting. You have to use `@set maxrows` to override the GUI setting. `@set maxrows -1` removes any limit. — ThatAintWorking, Jul 06 '15 at 21:00

score 3 · Answer 3 · answered Dec 08 '11 at 00:49

3

so apparently you need to have DBVisualizer Personal edition to set the maxrows, which the free edition doesn't support. You can get a free trial though. Then you can run something like...

@set maxrows 2000; 
select * from table;

If anyone knows how to do this in the free version please feel free to comment, thanks.

answered Dec 08 '11 at 00:49

HelloWorld

4,251
8
36
60

2

You need to run both statements at once (via the "Execute the buffer as an SQL script" button) for the `@set` to affect the `select`. – Noumenon Sep 23 '17 at 19:47

score 1 · Answer 4 · answered Apr 25 '15 at 07:52

select * from table where rownum < 10 would return 9 records for oracle.

But It varies db to db .

Sql server uses select top N fieldName from table.

For MySQL syntax changes as SELECT *FROM table LIMIT N

Maybe Some others use take , skip, etc... So using dbvisualizer , and its setting in the accepted answer is logical for cross db users. It doesn't bother you by varied sql syntax.

score 1 · Answer 5 · answered Dec 07 '11 at 17:26

1

From this page, it looks as though the maximum number of rows returned initially is specified within the Tool Properties dialog, on the General Settings tab, on the Table Data node in the Max Rows at First Display property.

answered Dec 07 '11 at 17:26

1

Thanks Mark, I did try this setting, but it only modified the "data" tab when you click on a table. The data tab simply displays the first X amount of rows in a table, but this setting doesn't modify the maxrows for queries. Appreciate the effort though :) – HelloWorld Dec 07 '11 at 17:29

dbvisualizer: set max rows in a select query

5 Answers5