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