“Я слышу и забываю. Я вижу и помню. Я делаю и понимаю.” – Конфуций.
Как разработчики SQL Server, мы часто оказываемся в ситуациях, когда нам нужны дополнительные функции и инструменты для эффективной разработки и отладки хранимых процедур, функций, пакетов и ad-hoc отчетов. Переключение между окнами, сессиями и инструментами может быть затратным по времени и неэффективным.
Давайте рассмотрим сценарий, когда мы хотим просмотреть все столбцы таблицы в Обозревателе объектов. К сожалению, мы не можем увидеть, установлено ли свойство идентификатора для столбца и значения начального заполнения и инкремента. Хотя мы можем сгенерировать оператор создания таблицы для просмотра этой информации, это требует дополнительных шагов и времени.
Теперь сравним два оператора создания таблицы:
Первый формат, сгенерированный SQL Query Analyzer:
CREATE TABLE [Orders] ( [OrderID] [int] IDENTITY (1, 1) NOT NULL , [CustomerID] [nchar] (5) COLLATE Cyrillic_General_CI_AS NULL , [EmployeeID] [int] NULL , [OrderDate] [datetime] NULL , [RequiredDate] [datetime] NULL , [ShippedDate] [datetime] NULL , [ShipVia] [int] NULL , [Freight] [money] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT (0), [ShipName] [nvarchar] (40) COLLATE Cyrillic_General_CI_AS NULL , [ShipAddress] [nvarchar] (60) COLLATE Cyrillic_General_CI_AS NULL , [ShipCity] [nvarchar] (15) COLLATE Cyrillic_General_CI_AS NULL , [ShipRegion] [nvarchar] (15) COLLATE Cyrillic_General_CI_AS NULL , [ShipPostalCode] [nvarchar] (10) COLLATE Cyrillic_General_CI_AS NULL , [ShipCountry] [nvarchar] (15) COLLATE Cyrillic_General_CI_AS NULL ) GO
Второй формат, сгенерированный пользовательской хранимой процедурой:
create table Orders ( OrderID int ,CustomerID nchar(5) ,EmployeeID int ,OrderDate datetime ,RequiredDate datetime ,ShippedDate datetime ,ShipVia int ,Freight money ,ShipName nvarchar(40) ,ShipAddress nvarchar(60) ,ShipCity nvarchar(15) ,ShipRegion nvarchar(15) ,ShipPostalCode nvarchar(10) ,ShipCountry nvarchar(15) )
Лично я предпочитаю второй формат. Однако важно отметить, что независимо от формата, который вы используете для создания таблицы, Query Analyzer или Enterprise Manager всегда вернут ваш код в первом формате. Поэтому я использую, разрабатываю и изменяю свои сценарии и сохраняю их в Visual Source Safe. Это позволяет мне открывать мой сценарий в формате, в котором я его разработал, что является важным для поддержания согласованности.
Хотя существуют сторонние инструменты для генерации кода из базы данных (обратная разработка), они могут не предоставлять все необходимые функции. Кроме того, стоит отметить, что SQL Server 2005 может не иметь этих функций.
При написании или отладке хранимой процедуры или скрипта Transact-SQL часто необходимо иметь информацию о таблице, такую как имена столбцов и типы данных, готовую к использованию в различных форматах. Чтобы упростить процесс набора текста, вы можете использовать пользовательскую хранимую процедуру с именем up_CT_Q (up означает пользовательскую процедуру, CT означает Cut Typing, Q означает Query, указывая, что в этой процедуре не используются операторы Insert, Update или Delete).
Хранимая процедура up_CT_Q возвращает информацию о таблице или представлении в различных форматах. Давайте рассмотрим несколько примеров, используя таблицу Customers в базе данных Northwind:
Формат 1:
CustomerID nchar(5) ,CompanyName nvarchar(40) ,ContactName nvarchar(30) ,ContactTitle nvarchar(30) ,Address nvarchar(60) ,City nvarchar(15) ,Region nvarchar(15) ,PostalCode nvarchar(10) ,Country nvarchar(15) ,Phone nvarchar(24) ,Fax nvarchar(24)
Этот формат полезен для быстрой проверки типа данных и длины столбца. Он может быть использован в качестве списка параметров входа для хранимой процедуры или в операторе создания таблицы для обратной разработки, когда требуется удалить таблицу и создать ее с измененным определением.
Формат 2:
Declare @CustomerID nchar(5) Declare @CompanyName nvarchar(40) Declare @ContactName nvarchar(30) Declare @ContactTitle nvarchar(30) Declare @Address nvarchar(60) Declare @City nvarchar(15) Declare @Region nvarchar(15) Declare @PostalCode nvarchar(10) Declare @Country nvarchar(15) Declare @Phone nvarchar(24) Declare @Fax nvarchar(24)
Этот формат полезен для объявления переменных в хранимой процедуре.
Формат 3, тип 1:
CustomerID ,CompanyName ,ContactName ,ContactTitle ,Address ,City ,Region ,PostalCode ,Country ,Phone ,Fax
В этом формате оператор Select или Insert легче читать и управлять. Если таблица содержит столбец со свойством идентификатора, вы должны перечислить все столбцы с ненулевыми значениями в операторе Insert. Этот формат также упрощает комментирование столбца при необходимости. Хотя SQL Query Analyzer может генерировать оператор Select, он становится сложным для чтения и изменения, когда таблица имеет несколько столбцов. В качестве bewt практики каждая строка сценария должна помещаться в ширину экрана.
Формат 3, тип 2:
Cust.CustomerID ,Cust.CompanyName ,Cust.ContactName ,Cust.ContactTitle ,Cust.Address ,Cust.City ,Cust.Region ,Cust.PostalCode ,Cust.Country ,Cust.Phone ,Cust.Fax
В дополнение к типу 1, все имена столбцов предваряются псевдонимом. Этот формат полезен для различных объединений и подзапросов.
Формат 3, тип 3:
Cust.CustomerID as [CustomerID] ,Cust.CompanyName as [CompanyName] ,Cust.ContactName as [ContactName] ,Cust.ContactTitle as [ContactTitle] ,Cust.Address as [Address] ,Cust.City as [City] ,Cust.Region as [Region] ,Cust.PostalCode as [PostalCode] ,Cust.Country as [Country] ,Cust.Phone as [Phone] ,Cust.Fax as [Fax]
Этот формат упрощает переименование столбцов, когда вывод должен иметь другие имена, чем таблица.
Формат 4:
CustomerID = @CustomerID ,CompanyName = @CompanyName ,ContactName = @ContactName ,ContactTitle = @ContactTitle ,Address = @Address ,City = @City ,Region = @Region ,PostalCode = @PostalCode ,Country = @Country ,Phone = @Phone ,Fax = @Fax
Этот формат используется в операторе “where” операторов Select, Update или Delete в хранимой процедуре.
Формат 5, если не указаны входные параметры:
Чтобы получить описание процедуры, просто запустите ее без параметров. Это предоставит следующую информацию:
ДОКУМЕНТАЦИЯ И ИСПОЛЬЗОВАНИЕ Пользовательская хранимая процедура up_CT_Q используется для обратной разработки и упрощения процессов написания и отладки сценариев. Первый входной параметр - имя таблицы. Второй входной параметр - номер формата, представляющий формат вывода. FormatNumber = 1: возвращается список имен столбцов и их типов данных. Это полезно в качестве списка параметров входа для хранимой процедуры. FormatNumber = 2: имена столбцов предваряются знаком @. Полезно для объявления переменных в хранимой процедуре. FormatNumber = 3 Тип 1: возвращает все имена столбцов в вертикальном формате таблицы с одним столбцом. В этом формате оператор Select или Insert легче читать и управлять, если есть необходимость закомментировать столбец. FormatNumber = 3 Тип 2: в дополнение к типу 1, все имена столбцов предваряются псевдонимом. FormatNumber = 3 Тип 3: упрощает переименование столбцов. FormatNumber = 4: используется в операторе "where" операторов Select, Update или Delete. Если имя таблицы не указано, процедура возвращает описание и примеры использования этой процедуры. Примеры, используя таблицу Customers в базе данных Northwind: Пример 1, Формат 1: exec up_CT_Q Customers,1 Пример 2, Формат 2: exec up_CT_Q Customers,2 Пример 3, Формат 3, Тип 1: exec up_CT_Q Customers,3,1 Пример 3, Формат 3, Тип 2: exec up_CT_Q Customers,3,2,Cust Пример 3, Формат 3, Тип 3: exec up_CT_Q Customers,3,3,Cust Пример 4, Формат 4: exec up_CT_Q Customers,4 Пример 5, для получения описания процедуры: exec up_CT_Q
Используя хранимую процедуру up_CT_Q, мы можем улучшить функциональность анализатора запросов SQL Server и упростить процессы написания и отладки сценариев. Эта процедура предоставляет различные форматы вывода, которые соответствуют различным потребностям, что упрощает работу с таблицами и столбцами.
Помните, что в качестве разработчиков важно находить способы оптимизации нашего рабочего процесса и экономии времени. Хранимая процедура up_CT_Q – всего лишь один пример того, как мы можем достичь этого в SQL Server.
Спасибо за чтение!