CODEWITHSUNDEEP
sql-serverssmssql-server-2008
Edward Tanguay
Edward Tanguay

Reputation: 193302

How to alter SQL in "Edit Top 200 Rows" in SSMS 2008

In SQL Server 2008 Management Studio, when I right click on a database table and choose Select Top 100 Rows, I can then e.g. easily add a ORDER BY statement to the SQL. That works fine.

But when I do choose Edit Top 200 Rows, I don't have the ability to alter the SQL (which makes it hard to find and edit a record just added in the 10,000 that are there.

I am quite sure I was able to do this in SQL Server 2000.

Is there any way in SMSS 2008 to alter the way the records are displayed when editing records?

Upvotes: 228

Views: 281035

Answers (8)

David Hall
David Hall

Reputation: 33143

If you right click on any result of "Edit Top 200 Rows" query in SSMS you will see the option "Pane -> SQL". It then shows the SQL Query that was run, which you can edit as you wish.

It's the SQL button on the following image: enter image description here

In SMSS 2012 and 2008, you can use Ctrl+3 to quickly get there.

Upvotes: 340

Fryann Martinez
Fryann Martinez

Reputation: 408

in SQL 2017 You can do it more easily in the toolbar to the right just hit
enter image description here

the SQL button then its gonna apear the query with the top 200 you edit until the quantity that You want and Execute the query and Done! just Edit

Upvotes: 8

Mayura Savaliya
Mayura Savaliya

Reputation: 440

The default to open/add rows to a table is Edit Top 200 Rows. If you have more than 200 rows, like me now, then you need to change the default setting. Here's what I did to change the edit default to 300:

  1. Go to Tools in top nav
  2. Select options, then SQL Service Object Explorer (on left)
  3. On right side of panel, click into the field that contains 200 and change to 300 (or whatever number you wish)
  4. Click OK and voila, you're all set!

Upvotes: 4

Shah
Shah

Reputation: 161

enter image description here

Follow the above image to edit rows from 200 to 100,000 Rows

Upvotes: 15

MemeDeveloper
MemeDeveloper

Reputation: 6782

Very quick and easy visual instructions to change this (and the select top 1000) for 2008 R2 through SSMS GUI

http://bradmarsh.net/index.php/2008/04/21/sql-2008-change-edit-top-200-rows/

Summary:

  • Go to Tools menu -> Options -> SQL Server Object Explorer
  • Expand SQL Server Object Explorer
  • Choose 'Commands'
  • For 'Value for Edit Top Rows' command, specify '0' to edit all rows

Upvotes: 34

Dumisani
Dumisani

Reputation: 3038

Ctrl+3 in SQL Server 2012. Might work in 2008 too

Upvotes: 9

brijwalsh
brijwalsh

Reputation: 51

Similar to David Hall's response, if you are a non-coder, it may be easiest to right-click within results, then choose Pane > Criteria. This allows you to adjust sort, add filters, etc... without adjusting SQL code.

Upvotes: 5

Scott
Scott

Reputation: 1751

You can also change the pop-up options themselves, to be more convenient for your normal use. Summary:

  1. Run the SQL Management Studio Express 2008
  2. Click the Tools -> Options

  3. Select SQL Server Object Explorer . Now you should be able to see the options

    • Value for Edit Top Rows Command
    • Value for Select Top Rows Command

  4. Give the Values 0 here to select/ Edit all the Records

Full Instructions with screenshots are here: http://m-elshazly.blogspot.com/2011/01/sql-server-2008-change-edit-top-200.html

Upvotes: 175

Related Questions