Often I have days where a large amount of my time is spent writing SQL code in SQL Server Management Studio (SSMS). Building tables, creating indices and verifying data are all common tasks. However, I often myself needing to checking which columns are in a table or to take a quick look at the data. I got tired of typing Select Top 10 * From [MyTable] more than five times per day. In order to get around typing that repeatedly I made a function called 'sp_top10'.
CREATE Procedure [dbo].[sp_top10]
@Table as varchar(100)
set nocount on
Declare @dbname as varchar(100)
Set @dbname = db_name()
Declare @MyQuery Varchar(max)
Select Top 10 *
From ' + @dbname + ' ..' + @Table + ' with(nolock)'
This stored procedure can be invoked by typing
exec sp_top10'YourTableHere' where you substitute in your own table name. The real power comes when you assign your new stored procedure to a shortcut. In SSMS you can do this by going to Tools > Options > Keyboard > Query Shortcuts . Simply add 'sp_top10' to an open key combination and then restart SSMS. You should now be able to highlight ( or doubleclick) a tablename and then use your new shortcut to view the top 10 records in that table. I set my shortcut to 'Ctrl+F1'.
-- Create Table To Hold Our Dataset
If Object_ID('tempdb..#TestData','U') is not null
Drop Table #TestData
Create Table #TestData
(ID bigint not null identity(1,1) ,
-- Quick Loop to get all lower case letters into our table
Declare @Counter Int;Set @Counter=97
While @Counter <=122
Declare @Sql Varchar(Max)
Set @SQL ='
Insert into #TestData (letter)
Select char(' + cast(@Counter as varchar) + ')
Set @Counter = @Counter+1
-- View all 26 records
-- Just view the top 10
You output should look similar to figure 1.
Feel free to edit the SP to suit your particular situation. I use this in conjunction with 'sp_help' which has a default shortcut of 'Alt+F1' in SSMS almost everyday. So I can select a tablename and 'Alt+F1' if I need to see what type of columns I have and then 'Ctrl+1' to view the first ten records of that table. Hopefully this will help save you time in your daily work.
Note: SSMS 2012 was used for this example.