How can I change the Select Top <N> Rows command in SQL SMS 2008

Sam Edwardson
Sam Edwardson used Ask the Experts™
on
How can I change the Select Top <N> Rows command in SQL SMS 2008.  I know that I can change the amount or rows that it returns under tools - options - SQL Server Object Explorer, but what I would like to do is instead of it running the script and putting in all the column names, just simply put a * in instead.  Like this:
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 * FROM etc etc

This way I dont get a whole list of column names running down my page.  All and any help is as always much appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try using row_number instead of TOP:

SELECT * FROM
(
select *, row_number() over( order by MyID) MyRowNumber
from MyTable
) T1
WHERE T1.MyRowNumber <= 1000

Author

Commented:
thanks for the reply rbeadie but I obviously didnt make my self clear.  The function comes from right clicking on the table and it automatically writes the query for you.  I want to edit this automated query so that it still pulls all the columns into the query but does not display them like this:
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [JobID]
      ,[MarketID]
      ,[CountryCode]
      ,[AccountNumber]
      ,[IssueNumber]
      ,[Status]
      ,[KeyRef]
      ,[Reference]
      ,[Position]
      ,[Skills]
      ,[Location]
      ,[StartDate]
      ,[Duration]
      ,[Contact]
      ,[Telephone]
      ,[Fax]
      ,[Email]
      ,[KeyLocations]
      ,[JobType]
      ,[Rate]
      ,[DatePosted]
      ,[URL]
      ,[JBEIssueNumber]
  FROM [Jobs].[dbo].[tJob]

I want the query to output like this:
SELECT TOP 1000 * FROM [Jobs].[dbo].[tJob]

Commented:
Ah -- sorry.  I understand your question now.  I'm not sure that you can change that functionality.  You can change the number of rows that is your default, but there are limited changes you can make to the ssms generated scripts.
Data Engineer
Commented:
You cannot change that. Thats a functionality provided to get the top 1000 records.

Author

Commented:
Guess I'll just have to put up with it then :-)  Thanks for the replies

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial