none
Select top 1000 rows

    Question

  • From THE object explorer pane of SSMS when i right-click a table and select the 'select top 1000 rows' option, automatically a query as the following is performed on the selected table:
    /****** Script for SelectTopNRows command from SSMS ******/
    SELECT TOP 1000 [Field1] 
    ,[Field2] 
    ,[Field3] 
    FROM [myDB].[dbo].[myTable]
    


    Is there any preferences option I can change so that the query would appear as:

    /****** Script for SelectTopNRows command from SSMS  ******/
    SELECT TOP 10 *
      FROM [myDB].[dbo].[myTable]
    

    or

    /****** Script for SelectTopNRows command from SSMS  ******/
    SELECT COUNT(*)
      FROM [myDB].[dbo].[myTable]
    
     
    Thanks in advance

    Oleg
    Tuesday, January 12, 2010 3:39 PM

Answers

  • I do not know of a way to do this.  The only thing configurable on those commands is the TOP number.  From the menu of SSMS 2008:
    Tools / Options / SQL Server Object Explorer / Commands

    There you will see that you can set the values for:
       Value for Select Top <n> Rows Command

    Other options:

    1. Instead of choosing the "Select Top <n> Rows", you can choose:
      Script Tables as / Select to / New Query Editor Window

    That will put the script into the window and you can edit it first before executing the query.

    2. Create a template that matches what you want more completely and then use it.  For example:
    SELECT TOP 100 *
    FROM <Database_Name, nvarchar(50), Favorite_db>.dbo.<Table_Name, nvarchar(50), >

    Then press the "Specify Values for Templates" button to fill in the variables.

    Neither of these are quite what you asked for, but they are useful when appropriate.

    RLF
    • Marked as answer by OlegKomarov Wednesday, January 13, 2010 2:01 PM
    Tuesday, January 12, 2010 4:46 PM

All replies

  • I do not know of a way to do this.  The only thing configurable on those commands is the TOP number.  From the menu of SSMS 2008:
    Tools / Options / SQL Server Object Explorer / Commands

    There you will see that you can set the values for:
       Value for Select Top <n> Rows Command

    Other options:

    1. Instead of choosing the "Select Top <n> Rows", you can choose:
      Script Tables as / Select to / New Query Editor Window

    That will put the script into the window and you can edit it first before executing the query.

    2. Create a template that matches what you want more completely and then use it.  For example:
    SELECT TOP 100 *
    FROM <Database_Name, nvarchar(50), Favorite_db>.dbo.<Table_Name, nvarchar(50), >

    Then press the "Specify Values for Templates" button to fill in the variables.

    Neither of these are quite what you asked for, but they are useful when appropriate.

    RLF
    • Marked as answer by OlegKomarov Wednesday, January 13, 2010 2:01 PM
    Tuesday, January 12, 2010 4:46 PM
  • I think in SSMS 2008 , you have 2 default options like 'select top 1000' and 'edit top 200' which can be changed from tool menu.

    Select top 10 *

    Though this can be changed from Tools / Options / SQL Server Object Explorer / Commands and then setting the value to 10 or 100 from 1000 - this is for whole SSMS and you cannot set that for only one table.

    Select count *

    I dont think it is possible to add 'select count' to the right click menu of a table - You can also post this in SQL connect site and see what microsoft has to say on this feedback. May be for quicker use you can store the command in an SP and call that whenever required.


    Thanks, Leks
    Tuesday, January 12, 2010 5:17 PM