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?
Solution 1:
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.
In SMSS 2012 and 2008, you can use Ctrl+3 to quickly get there.
Solution 2:
You can also change the pop-up options themselves, to be more convenient for your normal use. Summary:
- Run the SQL Management Studio Express 2008
- Click the Tools -> Options
-
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
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
Solution 3:
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