If you watch any SQL Server Developer, you will notice one particular task them doing every day frequently. It is they select the row from the table to see what are the various kinds of data it contains. Most of the tables are very big so it is always advisable to retrieve only a single row from the table. It is very cumbersome for developers to continuously write following code to retrieve a single row to see what the table contains.
SELECT TOP 1 * FROM TableName
I suggest you try to write above code and there is good chance that one has to look at the keyboard to type above code as it requires numbers. Even expert typist often has to look at the keyboard when they have to deal with Special Symbole (in our case *) and number (in our case 1) when typing subsequently. The counter argument is to not use above command but use SP_HELP or any other SP which describes the schema of the table. Well, this may be a good idea but we developer like to do things our own way. Just to be clear it is never a good idea to do any development on production server.
There is a shortcut how we can make our life easier if we use SQL Server Management Studio Shortcut. You can see following image where I am configuring SSMS shortcut.
Go to SSMS Menu >> Tools >> Options >> Environment >> Keyboard >> Query Shortcuts.
Now on the right side click on the preferred shortcut (in this case I am selecting CTRL + 3) and now in the column of the Stored Procedure type following statement
SELECT TOP 1 * FROM
Pay special attention that I have not mentioned the name of the table. The syntax is incomplete as we will SELECT the name of the table later on of SSMS Query Editor.
Once that is done, now go to SSMS and select any preferred table name. While the table name is selected type CTRL + 3 and you will notice that the query will run and will return a single row as a resultset. Now developer just has to select the table name and click on CTRL + 3 or your preferred shortcut key and you will be able to see a single row from your table.
This trick can be very helpful when a developer is debugging a long stored procedure with lots of a table name or while writing complex query.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
36 Comments. Leave new
Thank you Pinal, this is very useful advice.
Thanks a lot pinal sir, this is really helpful and informative and also saves time..
Is there any why to specify selected text value in T-SQL? for example: SELECT * FROM inner join SYS.tables t ON t.name = ”. I am thinking about order by PK and descending, as we always look for last table data…!
tag between < and > got removed! I had text “SELECTED_TEXT” in between.
Hello Vikas,
I spent nearly 10 minutes trying to understand your question, tried reading it in atleast 10 different ways but failed everytime.
But to make a generic statement, you can create a stored procedure that accepts parameter and perform what every search you want to do, just put the logic inside the stored procedure, if you think table name will change then make it dynamic sql sp and then execute the sp from keyboard short cuts.
If this doesn’t help then please elaborate your questions with some samples.
~ IM.
SELECT TOP 100 * FROM <SELECTED_TEXT> will list the top 100 rows, where selected_text will be the table name written in new query window, and as this post describes how we can use SQL Server short cut. Now in general if we have a transaction table, then we always look for latest entries, and if the table is just listing different status name then table may not have more that 10 to 100 records. This shortcut will be very helpful for tables having less records. But for me only, when I work with my database, I always have to see the last entry of any table, and as my database is about 5 year older, I have about 1 lakh to 1 crore entries in some of the tables.
Now speaking directly, how we can have trick to list table rows order by PK (if table has any PK and it’ll be there most of the time)?
What I was trying to do when i post my first comment is:
I was trying to get the PK column name’s list from sys.columns, now to use sys.columns, I may need to use sys,tables to get the object id,
I am wondering if we can use a sub query in order by clause!!
For example:
SELECT * FROM employees
ORDER BY (SELECT TOP 1 C.name + ‘ DESC’ AS orderby_text FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id AND t.name = ’employees’ AND c.is_identity = 1)
Oh pinal,
Its great help…
It is great article Pinal Dave.
I love ALT+F1… now there is no need to write “SELECT * FROM” or “SELECT TOP 10 FROM” :-)
Regards
Thank you Pinal Sir, this is very useful & timesaving idea
I initially misunderstood the method to execute this after creating the shortcut. The table name text must be highlighted in the query editor window, not in the object explorer.
I initially misunderstood the method to execute this after createing the shortcut. The table name text must be highlighted in the query editor window, not in the object explorer.
Thanks Pinal but I must be missing something. I’m not clear when you say “select table name.” I tried clicking the table name in the object explorer to highlight it but I get “incorrect syntax near ‘from'”. I tried typing the table name in the query window then hitting ctrl-3 but that does not work either…
Yes ! Ken I too am wondering about the same thing !!!
Once you have made changes as said in above article, close that connection and reopen/ New Query. Hopefully it will work
Only works for SQL 2012? Does not appear to work in SQL 2008
Rob, Once you have made changes as said in above article, close that connection and reopen/ New Query. Hopefully it will work.
i have done the procedure whatever u said ,but i cant get that short cut .Even it is showing nothing .Help me … regards
i am using SQL Server 2008
pratap you should be selecting the table name you have mentioned before entering the short cut
hi Pinal ,
this is kumar , having one query to ask…
there are two input dates
@d1= 01/02/2013
@d2= 28/02/2013
now what’s we write the query to generate bellow output.
output:
01/02/2013
01/02/2013
……
……
28/02/2013
Use below query…
declare @d1 date=’01/01/2013′
declare @d2 date=’02/28/2013′
;with cte_date (row_date)as(
select @d1
union all
select DATEADD(DD,1,row_date) from cte_date where row_date<@d2
)
select * from cte_date
I wish Oracle had this feature as well…
Found small Issue….
“SELECT TOP 1 * FROM ” looks grate, but I prefer some more data.
I add in shortcut “SELECT TOP10 * FROM ” (yes, there is a mistake)
Then I open New query and I try to run CTRL+3… Result?
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘from’.
Ok, what did I wrong? I found my mistake and I correct shortcut into “SELECT TOP 10 * FROM “. Than quick CTRL+3 and…
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘from’.
Oh! common… what`s wrong?!
I check one more time shortcut – OK
I saw comments to this post – all are happy…
I run SQL Profiler and I saw: “SELECT TOP10 * FROM my_table”
WTF?!
The key point is that I changed shortcut while it was opened query window.
SSMS load all shortcuts at the beginning and didn`t reload them later, even I changed one of them.
My solution is:
You change something in shortcuts? It will works only in new query windows. Old queries have “old” shortcuts.
Thank you.
I was sitting with this exact problem right now.
Thanks pinal
Sir ,this is not working in sql server 2008 r2 .
give me suggestion
I wish the application just let us choose our own shortcuts and not restricted to choose only 10 shortcuts. I would suggest to use a simple clipboard manager applications where you type a shortcut the query will fill in the editor.
Like I type ssf it expands to select * from
we can have our own custom shortcuts with table names and all
It’s great but intellisense in sql server does’nt work if i use this technique.
i have thousands of table in my db.
Is there any way to make the intellisense work as well when i use this technique. please help.
What do you mean intellisense doesn’t work?
Intellisense suggests table names available under database when we type sql query as “SELECT * FROM DB..”, but while using shortcut key we need to first type exact table name which is difficult in case of large number of tables.
I think this is what Vivek’s question is.