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

Answer 1

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

Answer 2

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

Answer 3

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;

Answer 4

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.