Helper Stored Procedures in SQL Server - Part 1

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)

as
Begin
set nocount on

Declare  @dbname as varchar(100)
Set @dbname = db_name()

Declare @MyQuery Varchar(max)
Set @MyQuery='
Select Top 10 *
From ' + @dbname + ' ..' + @Table + ' with(nolock)'

Exec (@MyQuery)
End

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'.

An example:

-- 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) ,
Letter varchar(1)
)

-- Quick Loop to get all lower case letters into our table
Declare @Counter Int;Set @Counter=97
While @Counter <=122
Begin
Declare @Sql Varchar(Max)
Set @SQL ='
Insert into #TestData (letter)
Select char(' + cast(@Counter as varchar)  + ')
'
--print @SQL
Exec (@Sql)
Set @Counter = @Counter+1
End

-- View all 26 records
Select *
From #TestData

-- Just view the top 10
exec sp_top10'#testdata'

You output should look similar to figure 1.

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.