Dave + SQL Server = Ingenious SQL….

In Part 1 of the Query Shortcuts, I demonstrated a quick SELECT * FROM keyboard shortcut. The problem with this approach is if your table is not in the default schema, you have to select the schema and table name. In this post I am going to demonstrate how you can use a custom stored procedure in the Query Shortcuts to return table data without selecting the schema.

First let’s look at the stored procedure.

Stored Procedure:

The idea behind the stored procedure is to find the schema for the table name that is passed in. If the same name is used in multiple schemas, the stored procedure will output both tables and all their data.

CREATE PROCEDURE [sp_GetTableData] (
   @TableName varchar(200))
AS 
   -- Remove any square brackets, we only want the table name
   SET @TableName = REPLACE(REPLACE(@TableName, '[', ''), ']', '')
 
   DECLARE @SelectFrom nvarchar(max);
   -- If temp table exists remove it
   IF OBJECT_ID('tempdb..#TableNames') IS NOT NULL 
      DROP TABLE #TableNames;
 
   -- Get a list of all tables that match in all schemas
   SELECT   TableName = s.NAME + '.' + o.name
   INTO     #TableNames
   FROM     sys.objects o
   JOIN     sys.schemas s ON o.schema_id = s.schema_id
   WHERE    o.name = @TableName
            AND o.type = 'U'
 
   -- Loop through all tables and output table results
   WHILE ( SELECT COUNT (1) FROM #TableNames ) >= 1 
      BEGIN
         SELECT TOP 1 @SelectFrom = N'SELECT * FROM ' + TableName
         FROM     #TableNames T
 
         EXECUTE sp_executesql @SelectFrom;
 
         DELETE TOP (1) FROM #TableNames
      END
 
   -- clean up
   DROP TABLE #TableNames

To use this query shortcut in all databases in the current server instance you can store this stored procedure in the Master database. Typically I would not recommend using the prefix “sp_”, however in this instance we need the procedure to behave as a system stored procedure. Also by using the prefix “sp_” SQL Server will look in the master database for the procedure before looking in the current database.

Query Shortcut Setup:

In order to setup a query short cut you will need to navigate to the SSMS Options Dialog:
(SQL 2012)

1. Open SSMS navigate to Tools –> Options…
2. After the dialog opens navigate to Environment –> Keyboard –> Query Shortcuts. This will open the Options dialog window shown below.
3. Enter “sp_GetTableData ” in any of the open text fields under Stored Procedure.
4. Click [OK]

NOTE: The new query shortcut will be available in all SSMS windows opened after the query shortcut is saved.

Now open any query, highlight any table name and press Ctrl+F1. Voila you have the contents of the highlighted table. Below we are looking at a snippet of the [dbo].[uspGetEmployeeManagers] procedure in the adventure works database.

Notice that this time the schema is not selected as a part of the table name. This gives us the ability to double click to highlight the table, and run our shortcut. This feature is handy when learning/debugging a stored procedure, function, view or any other db object.

A downfall with using the stored procedure version is the stored procedure has to be setup on each instance of SQL Server that you connect to in SSMS. This is because the stored procedure lives in the instance where as the Query Shortcut lives in SSMS. Part 1 does not have this same limitation.