CODEWITHSUNDEEP
sql-serversql-server-2008ssms
JoeFletch
JoeFletch

Reputation: 3960

SQL Server Search Database Objects "on the fly" with Native SSMS Functionality / SSMS Boost

TLDR: What I am ultimately looking for is to be able to highlight some text in the SQL Query Editor (which would be a partial object name), then press a keyboard shortcut (for example CTRL+5) which would search the current database for objects based on the matching the highlighted text. Then locate the object in the results, copy it (or the aliased version), press CTRL+R to close the grid and then paste. Then continue on with coding (and then probably do it again a minute later).

When coding / writing a query in SSMS using the SQL Query Editor, I constantly finding myself searching the database for objects to use in my current query. I use SSMS Boost, so my methodology is as follows.

  1. Open a new window (CTRL+N)

  2. Use SSMS AutoReplace to paste the following code. (The cursor is placed where the # is located.)

    DECLARE @t nvarchar(100)
SET @t = N'%#%'

SELECT
    o.Type,
    o.Type_Desc,
      '['+SCHEMA_NAME(o.schema_id)+'].['+o.name+']' AS ObjectName,
CASE WHEN Type IN (N'U') AND o.name LIKE N'%[_]%' THEN o.name + N' AS ' + STUFF(o.name, 1, CHARINDEX(N'_', o.name), N'') END AS Alias
FROM sys.objects AS o
WHERE o.name like @t
AND RIGHT(REPLACE(o.name, N'sel', N''), 3) NOT IN (N'upd', N'del', N'ins', N'bdc')
--AND Type  IN (N'U')
ORDER BY o.name

  3. Type or paste the text

  4. Press F5 to execute the query
  5. Copy the object name from the results to use in the query
  6. Close the window with CTRL+F4
  7. Paste the table (and alias) in the results

I've gotten pretty quick at doing this, but I still feel like there is a fair amount of time wasted.

In order to attempt to solve this problem and increase functionality with SSMS Boost, I have tried to use the Macro Functionality

  1. Copy highlighted text - Edit.Copy, (Global::Ctrl+C,Global::Ctrl+Ins)
  2. Open a new window - File.NewQuery, (Global::Ctrl+N)
  3. run the autoreplacement - ??? Or use another method of pasting the above SQL
  4. Paste the text - Edit.Paste, (Global::Ctrl+V,Global::Shift+Ins) SSMS Boost Macro

Since there does not appear to be the ability to add parameters / free text into the macros, I get stuck and cannot figure out what else to do. I tried posting on the SSMS Boost Forums, but I did not get much of a response.

I also tried to incorporate the native SSMS keyboard shortcuts without any success. SSMS keyboard shortcuts

The following 2 queries work well with the SSMS shortcuts when an object (view or table) is valid. It also appears that the shortcuts work because the functionality allows for the highlighted text to be added to the end of the shortcut code / SQL.

SELECT COUNT(*) AS [Count] FROM --to get a quick count of the number of records in a table
SELECT TOP 100 * FROM --to get a glimpse of the data in the highlighted table or view

Finally, I looked into using SSMS Templates (which seems like pretty darn good functionality), but I could not figure out how to use them on the fly with a keyboard shortcut.

Upvotes: 2

Views: 456

Answers (2)

Andrei Rantsevich
Andrei Rantsevich

Reputation: 2935

We have implemented this in SSMSBoost: select any string in Query Editor and press Shift-F2. This will open our "Database orbject search" dialog, filtered by selected string.

(Sorry if you have got not reply on our forum, maybe we have implemented your proposal and have forgotten to reply to you)

Upvotes: 2

Ed Harper
Ed Harper

Reputation: 21505

Here's a solution using SSMS keyboard shortcuts, which appears to work for me in SSMS 2016. Assign the following to a key combination (one long line):

EXEC('CREATE PROC #up_helpalias @t sysname AS SELECT o.Type, o.Type_Desc, ''[''+SCHEMA_NAME(o.schema_id)+''].[''+o.name+'']'' AS ObjectName, CASE WHEN Type IN (N''U'') AND o.name LIKE N''%[_]%'' THEN o.name + N'' AS '' + STUFF(o.name, 1, CHARINDEX(N''_'', o.name), N'''') END AS Alias FROM sys.objects AS o WHERE o.name like N''%'' + @t + ''%'' AND RIGHT(REPLACE(o.name, N''sel'', N''''), 3) NOT IN (N''upd'', N''del'', N''ins'', N''bdc'') ORDER BY o.name;DROP PROC #up_helpalias;');exec #up_helpalias

It works by creating and then executing a temporary stored procedure based on your query - a more readable version of the statement definition is below. Notice that the procedure includes code to drop itself at the end of execution:

CREATE PROC #up_helpalias
    @t sysname
AS
BEGIN
    SELECT
    o.Type,
    o.Type_Desc,
        '['+SCHEMA_NAME(o.schema_id)+'].['+o.name+']' AS ObjectName,
    CASE WHEN Type IN (N'U') AND o.name LIKE N'%[_]%' THEN o.name + N' AS ' + STUFF(o.name, 1, CHARINDEX(N'_', o.name), N'') END AS Alias
    FROM sys.objects AS o
    WHERE o.name like N'%' + @t + '%'
    AND RIGHT(REPLACE(o.name, N'sel', N''), 3) NOT IN (N'upd', N'del', N'ins', N'bdc')
    ORDER BY o.name

    DROP PROC #up_helpalias
END

followed by:

exec #up_helpalias

which executes the procedure using the value passed in from the keyboard shortcut.

Upvotes: 1

Related Questions