Tired of typing SQL scripts like "SELECT TOP 10 * FROM" and other similar scripts. How to create an alias/shortcut in SSMS?

Question

While doing a lot of research at my work I keep writing generic scripts like "SELECT TOP 10 * FROM" etc.

Is there a way to for me to write something like a snippet so that when i type "ss" + Space/Tab it will insert the "SELECT TOP 10 * FROM" script?

I have a number of scripts that I've noticed i use a lot and i would like to create some aliases for them...

You could use Function (sql server)... Parametrizing table name. How to create a function go to https://learn.microsoft.com/it-it/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver15 — usr4217, Oct 24 '20 at 12:14
SSMS does support Code Snippets which allows you to have predefined (optionally parameterized) blocks of code inserted on command. But it doesn't support keyboard shortcuts for them (although it's in the spec for the snippet file). https://learn.microsoft.com/en-us/sql/ssms/scripting/add-transact-sql-snippets?view=sql-server-ver15#:~:text=Importing%20a%20Snippet%20Open%20SQL%20Server%20Management%20Studio.,the%20.snippet%20file%2C%20and%20click%20the%20Open%20button. is the documentation for this functionality, it may be more useful if you have requirements other than the top 10 * — Andrew Sayer, Oct 24 '20 at 12:56
[SQL Prompt](https://www.red-gate.com/products/sql-development/sql-prompt/) by Redgate. — TGnat, Oct 24 '20 at 13:54
Yes @TGnat some vendor tools do it. We use a competitor to the one you named. I never use that functionality tho — SteveC, Oct 24 '20 at 15:42
@TGnat, thanks for the tip. I actually use RedGate but didn't know about SQL Prompt. Found it very easy to add a snippet. Thanks! — Nathan, Oct 24 '20 at 15:47

score 1 · Answer 1 · answered Oct 24 '20 at 13:03

You could go outside SSMS and rely on a hotkey that's setup (and it will fire regardless of if you are in SSMS). Autohotkey (https://www.autohotkey.com/) can be used for this implementation https://www.autohotkey.com/docs/Tutorial.htm#s12 describes how to achieve your exact scenario.

score 1 · Accepted Answer · answered Oct 24 '20 at 13:28

While you could create a code snippet, it is tiresome to select the snippet.

It still needs: CTRL-K CTRL-X M <tab> S <tab> to get:

SELECT TOP 10 * FROM Table1 and being able to type the needed tablename.

snippet:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
    <_locDefault _loc="locNone" />
    <_locTag _loc="locData">Title</_locTag>
    <_locTag _loc="locData">Description</_locTag>
    <_locTag _loc="locData">Author</_locTag>
    <_locTag _loc="locData">ToolTip</_locTag>
</_locDefinition>
    <CodeSnippet Format="1.0.0">
        <Header>
            <Title>Select</Title>
                        <Shortcut></Shortcut>
            <Description>SELECT TOP 10 * FROM ....</Description>
            <Author>?</Author>
            <SnippetTypes>
                <SnippetType>Expansion</SnippetType>
            </SnippetTypes>
            <Shortcut>select</Shortcut>
        </Header>
        <Snippet>
            <Declarations>
                                <Literal>
                                    <ID>TableName</ID>
                                    <ToolTip>Name of the table</ToolTip>
                                    <Default>Table1</Default>
                                </Literal>
            </Declarations>
            <Code Language="SQL"><![CDATA[
SELECT TOP 10 * FROM  $TableName$
]]>
            </Code>
        </Snippet>
    </CodeSnippet>
</CodeSnippets>

Saven this to (i.e.) "select.snippet", and Import in SSMS under 'Tools/Code Snipper Manager'

It is unclear, to me, what the section <Shortcut>select</Shortcut> has for value ...

According to next site, shortcuts is a "Won't Fix" back in 2013: https://dba.stackexchange.com/questions/166432/shortcuts-for-code-snippets

i had used snippet in the past but i was unable to transfer this scripts from sql server to other tools. AutoHotkey has servered the purpose well. — PAS, Mar 04 '22 at 13:19

score 0 · Answer 3 · edited Oct 24 '20 at 15:54

0

No, there is no way to do this inside SQL Server (and I don't think in any other databases).

Basically, you are looking for some sort of macro preprocessing and that is not part of the SQL language although some databases might support it in their scripting language or some tools might support it.

Hmmm . . . it occurs to me that you could use a stored procedure. If you wanted to write:

exec top10 'tablename';

Then define the procedure as:

create procedure top10 (@t nvarchar(max)) as
begin
    declare @sql nvarchar(max);
    set @sql = 'select top (10) * from [' + @t + ']';
    exec sp_executesql @sql;
end;

edited Oct 24 '20 at 15:54

Luuk

12,245
5
22
33

answered Oct 24 '20 at 12:52

Gordon Linoff

1,242,037
58
646
786

SQL Macros are part of Oracle from 20c onwards, they've been backported to 19.7 as well. However, I would interpret this requirement as something that wants to be implemented by the client side code editor (SSMS in this case). – Andrew Sayer Oct 24 '20 at 12:59
1

The "[AS](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15)" was missing before "begin" ... – Luuk Oct 24 '20 at 15:51

score 0 · Answer 4 · answered Mar 04 '22 at 13:17

0

I have used AuthoHotkey for this purpose e.g. I use ssf for select * from. Here is an example.

:oc:ssf::
SendInput,{Home}SELECT TOP (100) * FROM  {End} WITH (NOLOCK)^{Left 4}{Left 1}
return

answered Mar 04 '22 at 13:17

PAS

1,791
16
20

Tired of typing SQL scripts like "SELECT TOP 10 * FROM" and other similar scripts. How to create an alias/shortcut in SSMS?

4 Answers4