Перевірка, як розширені властивості SQL Server можна використовувати для відстеження змін на SQL-об'єктах, таких як таблиці, подання та збережені процедури.
Проблема та фон
Під час моєї роботи з базами даних SQL я часто стикався з проблемою пошуку хорошого способу відстеження того, хто вносить зміни до таблиць, збережених процедур чи інших SQL-об'єктів.
Так, ви можете використовувати систему керування версіями, але часто буває так, що виникає розрив між тим, що зараз є в базі даних, і тим, що є у вашому репозиторії.
Іноді просто зрозуміти, чи синхронізовані певні SQL-об'єкти з вашим репозиторієм, може бути важко.
Ви могли б також використати SQL Server change tracking, але це фокусується на змінах DML або модифікаціях даних.
Альтернативно, можна реалізувати тригери для запису змін об'єктів бази даних чи таблиць. Обидва підходи працюватимуть, але, як і додавання коментарів до комітів у git, корисно мати пояснення для змін, так само розширені властивості на SQL-об'єктах можуть бути корисними.
Часто, намагаючись зрозуміти, як зміна відбулася або чому вона була зроблена, історія git є хорошим місцем для початку. Використовуючи історію git, ми бачимо, хто що зробив, коли і чому, і це дозволяє нам зрозуміти поточний стан файлу чи системи.
Я хотів мати таке для SQL (з репозиторієм чи без нього). Тому я дослідив розширені властивості SQL Server, і вони можуть бути життєздатним рішенням.
Так, існують платні варіанти, такі як:
Однак більшість програмного забезпечення для керування версіями не є безкоштовним, а якщо воно і безкоштовне, то дуже обмежене, і можливості налаштування зазвичай або обмежені, або відсутні.
Рішення
Я кажу “Рішення”, а не “Єдине рішення”, тому що існує багато інших, і можливо, є кращі варіанти, ніж той, який я пропоную тут, але те, що мені подобається в використанні розширених властивостей SQL Server, це те, що це просто, а я люблю прості рішення, тому що вони легко підтримуються, оновлюються та розширюються, а так, прості рішення також іноді обмежені, але я вважаю, що це достатньо просте, щоб надавати цінність, при цьому залишаючись зручним у використанні.
Отже, що ж включає наше рішення? На базовому рівні, я хотів відстежувати версію та зміни, зроблені на SQL-об'єкті, в основному на таблицях і збережених процедурах, щоб я міг зрозуміти історію SQL-об'єкта.
Крім того, я хотів додати посилання на файл репозиторію для SQL-об'єкта, щоб я міг швидко отримати огляд змін git, не переходячи самостійно через репозиторій.
І наостанок, я хотів включити короткий опис того, для чого використовується збережена процедура, таблиця чи інший SQL-об'єкт.
Наступне питання, яке я поставив перед собою, це як я міг би додати ці дані до SQL-об'єктів, і після деяких досліджень я знайшов розширені властивості SQL Server, і вони відповідають усім моїм вимогам.
Що таке розширені властивості SQL Server?
Скорочено, розширені властивості дозволяють додавати власні властивості до об'єктів вашої бази даних. Ці розширені властивості можуть бути додані до SQL-об'єктів, таких як таблиці, збережені процедури, стовпці таблиць, подання та обмеження, серед інших.
Розширені властивості складаються з імені та значення, ось і все.
Використовуючи це поєднання імені та значення, ми можемо легко додати інформацію про відстеження версій.
Я пішов далі, використовуючи JSON для збереження моєї інформації про відстеження. Завдяки JSON я міг легко структурувати більш складну інформацію в секції значення, що дозволяло спростити запити в SQL, про що я розповім пізніше.
Нижче наведено приклад розширеної властивості, яка використовується для відстеження версії.
Приклад розширеної властивості SQL Server
Варіанти використання
Ви можете робити багато різних речей за допомогою розширених властивостей, окрім того, що я поясню в цій статті, таких як:
- Додавання підписів для таблиць, подань та/або стовпців
- Визначення формату стовпця
- Додавання метаданих для стовпця або SQL-об'єкта
Як створити/редагувати розширену властивість
Є два основних способи створення та редагування розширених властивостей: через SSMS або за допомогою SQL-запиту. Ми розглянемо обидва варіанти.
SSMS
- Перейдіть до вашого SQL-об'єкта (таблиця, подання, збережена процедура тощо) та клацніть правою кнопкою миші
- Натисніть “Властивості”
- Потім натисніть “Розширені властивості”
- Тепер ви зможете додати ім’я та значення
- Після завершення натисніть “ОК”, щоб зберегти розширені властивості
Дивіться нижче для довідки:
Створення розширених властивостей через SSMS
Щоб відредагувати розширену властивість, потрібно виконати ту ж саму процедуру, що й вище, але поруч з розширеною властивістю натисніть три крапки в кінці. Це відкриє нове вікно, де ви зможете оновити властивість, після чого натисніть “ОК”.
Щоб видалити розширену властивість, перейдіть на сторінку розширених властивостей і після того, як ви виділите її, натисніть “Видалити”, після чого натисніть “ОК”.
SQL-запит
Ви також можете додавати їх за допомогою SQL-запиту, для цього ви можете використати системну збережену процедуру sp_addextendedproperty.
При використанні sp_addextendedproperty ви повинні вказати мінімум ім’я та значення (значення має максимальний розмір 7500 байт).
Нижче наведено приклад виклику запиту для додавання деяких деталей до збереженої процедури:
EXEC sys.sp_addextendedproperty @name = N'Version',
@value = N'1',
@level0type = 'SCHEMA', @level0name = N'dbo',
@level1type = 'PROCEDURE', @level1name = N'TestProc';
GO
У наведеному запиті ми вказуємо різні аргументи для системної збереженої процедури, а саме:
- @Name — ім’я властивості, яку потрібно додати
- @Value — значення, яке буде асоційоване з властивістю
- @Level0Type — тип об'єкта рівня 0, у нашому випадку це схема
- @Level0name — ім’я об'єкта рівня 0
- @Level1Type — тип об'єкта рівня 1, у нашому випадку це процедура
- @Level1name — ім’я об'єкта рівня 1
Після виконання наведеного запиту ви повинні побачити нижче розширену властивість на вашому SQL-об'єкті:
Приклад розширених властивостей
Ви могли помітити, що рівні вказуються як аргументи під час виклику sp_addextendedproperty системної збереженої процедури, об'єкти в SQL Server класифікуються на три рівні: від 0 до 2.
Деталі нижче:
- Рівень 0 є найвищим і визначається як об'єкти, що містяться в межах бази даних.
- Об'єкти рівня 1 містяться в межах схеми або користувацького простору.
- Об'єкти рівня 2 містяться в об'єктах рівня 1 (наприклад, стовпець є об'єктом рівня 2 для таблиці, яка є об'єктом рівня 1).
Посилання на об'єкт одного рівня повинні бути кваліфіковані іменами вищих рівнів, які їх містять.
У нашому прикладі вище нам довелося вказати ім’я схеми (рівень 0) та ім’я процедури (рівень 1), щоб додати до них розширені властивості.
Щоб оновити розширену властивість, ви можете використати системну збережену процедуру sp_updateextendedproperty, яка використовує подібні аргументи до процедури створення.
Нарешті, якщо ви хочете видалити розширену властивість, можете використати системну збережену процедуру sp_dropextendedproperty
Як переглядати розширені властивості
Окрім використання SSMS, ви також можете використовувати SQL-запит для перегляду ваших розширених властивостей.
Ви можете запитати таблицю sys.extended_properties, щоб переглянути ваші розширені властивості у вигляді таблиці.
Щоб запитати конкретний об'єкт за допомогою таблиці sys.extendedproperties, потрібно отримати majorid вашого об'єкта, для цього можна використати різні таблиці, такі як sys.tables або sys.procedures. Це поверне таблицю з objectid, який можна використовувати як majorid для отримання розширених властивостей. Для цього використовуйте запит нижче. Я вказав ім’я своєї збереженої процедури “TestProc” і отримав object ID з sys.procedures.
SELECT * FROM sys.extended_properties WHERE major_id IN (
SELECT object_id FROM sys.procedures WHERE name = 'TestProc'
)
Результати запиту
Альтернативно ви можете використовувати fn_listextendedproperty, ця системна функція приймає різні аргументи, які ви можете вказати для пошуку ваших розширених властивостей.
Вам потрібно буде вказати різні рівні об'єктів за типом і ім’ям, наприклад, щоб отримати розширені властивості наших збережених процедур, виконаємо запит нижче:
SELECT objtype, objname, name, value
FROM fn_listextendedproperty(default, 'schema', 'dbo', 'procedure', 'TestProc', default, default);
Результат
Результат
Рішення
Тепер, коли ми розглянули, що таке розширені властивості і як їх створювати та переглядати, давайте подивимося, як ми можемо використовувати розширені властивості для реалізації базового відстеження змін.
Як згадувалося, я хочу додати кілька розширених властивостей до своїх SQL-об'єктів. Нижче наведені різні розширені властивості, які ми будемо додавати, разом з коротким описом їх призначення:
- Version: номер версії
- Synopsis: короткий опис призначення SQL-об'єкта
- RepositoryLink: Посилання на файл у репозиторії для отримання інформації про контроль версій
-
ChangeLog: JSON журнал змін, що містить відомості про зміни, внесені до SQL-об'єкта.
Деталі змін: -
Рівень 0 — це найвищий рівень і визначається як об'єкти, які знаходяться в межах бази даних.
-
Об'єкти рівня 1 знаходяться в межах схеми або користувацького простору.
-
Об'єкти рівня 2 містяться в об'єктах рівня 1 (наприклад, стовпець є об'єктом рівня 2 для таблиці, яка є об'єктом рівня 1).
Посилання на об'єкт одного рівня повинні бути кваліфіковані іменами об'єктів вищих рівнів, які їх містять.
У нашому прикладі вище ми повинні були вказати схему (рівень 0) та ім'я процедури (рівень 1), щоб додати до них розширені властивості.
Щоб оновити розширену властивість, ви використовуєте системну збережену процедуру sp_updateextendedproperty, яка використовує подібні аргументи до процедури створення.
Нарешті, якщо ви хочете видалити розширену властивість, можна використати системну збережену процедуру sp_dropextendedproperty
Як переглядати розширені властивості
Окрім використання SSMS, ви також можете використовувати SQL-запит для перегляду ваших розширених властивостей.
Ви можете запитати таблицю sys.extended_properties, щоб переглянути ваші розширені властивості у вигляді таблиці.
Щоб запитати конкретний об'єкт за допомогою таблиці sys.extendedproperties, потрібно отримати majorid вашого об'єкта, для цього можна використати різні таблиці, такі як sys.tables або sys.procedures. Це поверне таблицю з objectid, який можна використовувати як majorid для отримання розширених властивостей. Для цього використовуйте запит нижче. Я вказав ім'я своєї збереженої процедури “TestProc” і отримав object ID з sys.procedures.
SELECT * FROM sys.extended_properties WHERE major_id IN (
SELECT object_id FROM sys.procedures WHERE name = 'TestProc'
)
Результати запиту
Альтернативно ви можете використовувати fn_listextendedproperty, ця системна функція приймає різні аргументи, які ви можете вказати для пошуку ваших розширених властивостей.
Вам потрібно буде вказати різні рівні об'єктів за типом і ім'ям, наприклад, щоб отримати розширені властивості наших збережених процедур, виконаємо запит нижче:
SELECT objtype, objname, name, value
FROM fn_listextendedproperty(default, 'schema', 'dbo', 'procedure', 'TestProc', default, default);
Результат
Результат
Рішення
Тепер, коли ми розглянули, що таке розширені властивості і як їх створювати та переглядати, давайте подивимося, як ми можемо використовувати розширені властивості для реалізації базового відстеження змін.
Як згадувалося, я хочу додати кілька розширених властивостей до своїх SQL-об'єктів. Нижче наведені різні розширені властивості, які ми будемо додавати, разом з коротким описом їх призначення:
- Version: номер версії
- Synopsis: короткий опис призначення SQL-об'єкта
- RepositoryLink: Посилання на файл у репозиторії для отримання інформації про контроль версій
- ChangeLog: JSON журнал змін, що містить відомості про зміни, внесені до SQL-об'єкта.
Нарешті, ми розглянули приклад використання розширених властивостей як журналу змін для SQL-об'єктів.
Сподіваюся, ви отримали корисні відомості на цьому шляху і побачили переваги використання розширених властивостей у MS SQL.
Посилання
- https://www.mssqltips.com/sqlservertip/5384/working-with-sql-server-extended-properties/
- https://www.sqlservercentral.com/articles/extended-properties-introduction
- https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/
- https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16
Перекладено з: Tracking SQL Changes using Extended Properties