SQL Server 2012: Фрагменты кода на T-SQL

Салим Хакани

Вспомните о сериях команд, которые приходится писать при создании триггеров, таблиц, хранимых процедур и даже инструкций Select. А теперь представьте, что все эти команды определены и готовы к использованию. Можно значительно сократить время и усилия по кодированию, если использовать новые фрагменты кода на T-SQL в SQL Server 2012.

Фрагменты кода позволяют быстро строить инструкции T-SQL без необходимости помнить команды и их синтаксис. Такую функциональность можно использовать для сокращения времени разработки и повышения производительности своих разработчиков и администраторов БД.

Фрагменты в SQL Server 2012 основаны на XML с предопределенными полями и значениями. При использовании фрагмента кода на T-SQL эти поля выделяются и можно переходить по полям, изменяя значения, как требуется.

Для простоты использования фрагменты кода разбиты на категории. Просматривать и выбирать разные фрагменты можно, ориентируясь на категории. В SQL Server 2012 представлены три вида фрагментов:

Фрагменты по умолчанию (или фрагменты расширения) Существуют фрагменты кода для различных команд T-SQL, которые можно быстро вставить в свой код при создании таблиц, хранимых процедур, триггеров и т. п.
Фрагменты окружения Это шаблоны кода, помогающие реализовывать такие конструкции кода, как Begin End, If, While и другие.
Пользовательские фрагменты Можно создавать собственные фрагменты, которые отображаются в меню фрагментов.

Создание пользовательских фрагментов

Узнаем, как можно создавать пользовательские фрагменты и добавлять их в меню фрагментов. Фрагменты создаются и используются в процессе, состоящем из трех шагов:

Создание фрагмента с использованием XML.
Регистрация фрагмента в консоли SQL Server Management Studio (SSMS).
Вызов фрагмента при работе в редакторе запросов.

По умолчанию все фрагменты кода на T-SQL хранятся в следующей папке в виде файлов с расширением .snippet: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SQL\Snippets\1033.

Шаг первый. Создание файла фрагмента кода на T-SQL с использованием XML
Вот фрагменты, которые можно использовать для написания инструкции Select для любой таблицы (он также позволяет использовать инструкцию CASE для проверки равенства в столбце):

CASE_END.SNIPPET File
<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>Case-End</Title>
<Description> Insert Case...End Construct. </Description>
<Author> Saleem Hakani (Microsoft Corporation) </Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Code Language="SQL">
<![CDATA[
Select <Column_Name1>, <Column_Name2>, <Column_Name3>, <Column_Name4>=
  CASE <Column_Name4>
    WHEN '<value>' THEN '<Result>'
    WHEN '<value>' THEN '<Result>'
    WHEN '<value>' THEN '<Result>'
      WHEN '<value>' THEN '<Result>'
    ELSE 'Value not found'
  END,
<Column_Name5>, <Column_Name6>
From <Table_Name>
Go
]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

Шаг 2. Регистрация фрагмента в SSMS

Готовые файлы фрагментов регистрируются в SSMS с помощью диспетчера фрагментов кода (Code Snippets Manager). Можно создать новую папку на основе категории фрагментов или импортировать отдельные фрагменты в папку My Code Snippets.

Папка с фрагментами создается так:

Откройте консоль SSMS.
В меню Tools выберите Code Snippets Manager. Откроется окно диспетчера фрагментов.
Щелкните кнопку Add.
Перейдите к папке, содержащей файл CASE_END.Snippet, и щелкните кнопку Select Folder.

Далее надо импортировать фрагмент в консоль SSMS:

Откройте консоль SSMS.
В меню Tools выберите Code Snippets Manager.
Щелкните кнопку Import в нижней части окна.
Перейдите к папке, содержащей файл CASE_END.Snippet, выберите этот файл и щелкните кнопку Open.

Шаг 3. Вызов и вставка фрагмент T-SQL в окне редактора кода

Теперь у вас есть фрагмент по имени CASE_END, который можно вызывать в редакторе. Для этого надо нажать CTRL + K + X и выбрать категорию, в которой хранится нужный фрагмент. Вы также можете щелкнуть правой кнопкой в окне редактора кода и выбрать Insert Snippet.

Используя описанные шаги можно создавать фрагменты кода на T-SQL и регистрировать их в SSMS. Можно также создавать сложные фрагменты различных стандартных задач, значительно облегчив этим свою работу в SQL Server.

Решения на основе фрагментов

Представьте, что вы разработчик или администратор БД, отвечающий за безопасность своих серверов. На сервере SQL Server имеется 500 имен входа, но вы не знаете, какие роли назначены тем или другим именам входа. Если проверять имена входа по одному, это может занять часы или даже дни. Автоматизация быстрой проверки имен входа на уровне сервера позволило бы сократить время кодирования, а также повысило корректность кода, а также производительность работы разработчика и администратора БД.

Вот фрагмент кода, который позволяет быстро просматривать имена входа на уровне сервера вместе с их ролями и разрешениями. Следующий фрагмент SecuritySPY выводит список имен входа на уровне сервера вместе с назначенным им ролями:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>SQL_SecuritySPY - By Saleem Hakani (Microsoft Corporation)</Title>
<Description> Shortcut for checking SQL Server Server Role Permissions </Description>
<Author> Saleem Hakani (Microsoft Corporation) </Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Code Language="SQL">
<![CDATA[
--Author: Saleem Hakani (Microsoft Corporation)
--Website: Http://sqlcommunity.com
Select
                'Login Name'= Substring(upper(SUSER_SNAME(SID)),1,40),
                'Login Create Date'=Convert(Varchar(24),CreateDate),
                'System Admin' = Case SysAdmin
                                                When 1 then 'YES (VERIFY)'
                                                When 0 then 'NO'
                End,
                'Security Admin' = Case SecurityAdmin
                                                When 1 then 'YES (VERIFY)'
                                                When 0 then 'NO'
                End,
                'Server Admin' = Case ServerAdmin
                                                When 1 then 'YES (VERIFY)'
                                                When 0 then 'NO'
                End,
                'Setup Admin' = Case SetupAdmin
                                                When 1 then 'YES (VERIFY)'
                                                When 0 then 'NO'
                End,
                'Process Admin' = Case ProcessAdmin
                                                When 1 then 'YES (VERIFY)'
                                                When 0 then 'NO'
                End,
                'Disk Admin' = Case DiskAdmin
                                                When 1 then 'YES (VERIFY)'
                                                When 0 then 'NO'
                End,
                'Database Creator' = Case DBCreator
                                                When 1 then 'YES (VERIFY)'
                                                When 0 then 'NO'
                End
                from Master.Sys.SysLogins order by 3 desc
Go
]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

Установив фрагмент SecuritySPY, вы сможете вызывать его в редакторе запросов, как описано выше в этой статье. Можно также щелкнуть правой кнопкой мыши в редакторе запросов и выбрать в контекстном меню Insert Snippet.

Создание и использование фрагментов позволяет упорядочить задачи по управлению SQL Server. Наличие заранее определенных команд избавляет от необходимости каждый раз делать одну и ту же работу.