Monday, January 27, 2014

SSMS Tips : Customize Record Count Retrieved through Object Explorer

So here's another SSMS Object Explorer tip for you
There was a question asked in one of the forums couple of days back.
In SSMS object explorer you've the option to Edit and Select records from a table

The question was whether you can customize this and change the row count to a different value ie say Edit Top 1000 rows, Select Top 10000 rows etc.
As specified before, I'm not a big fan of doing these activities through SSMS. My preferred approach has been to do them through T-SQL coding using SELECT TOP and UPDATE statements. However the above question prompted me to do a brief research on this and I was able to get the method to change these values which I'm sharing through this blog.
The settings can be found by accessing SSMS Options from Tools menu item on top. This will open up Options window.
Go to Commands tab under SQL Server object explorer item in left navbar and you can see the below window
As you see it has the placeholders where you can type in a value which will get set for the corresponding object explorer options. I went ahead and changed value for Edit Top N to 1000 and clicked OK to save.
Now if you went back to Object Explorer and right click on a table you'll get option modified as below
As you see from the above your change got applied to the option.
Hopefully this helps anyone who want to apply similar customization to SSMS object explorer options.