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.
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:
- Open SSMS navigate to Tools –> Options…
- After the dialog opens navigate to Environment –> Keyboard –> Query Shortcuts. This will open the Options dialog window shown below.
- Enter “sp_GetTableData ” in any of the open text fields under Stored Procedure.
- 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.