В процессе разработки баз данных часто возникает следующая ситуация. В процессе развития базы данных в ней создаются различные объекты: файловые группы, схемы, таблицы, роли. Когда объектов становится много, программист может забыть о назначении некоторых из них, например, таблиц и их столбцов, об особенностях работы процедур. Разработчики, которые работают над одной частью базы данных могут не иметь достаточных знаний об особенностях работы других частей базы, которые были созданы другими разработчиками. Если разработчик создал таблицу, схему или хранимую процедуру, а затем долгое время с ней не работал, то возвращаясь к этим объектам, он не всегда может вспомнить об их назначении и особенностях.
Для того. чтобы база данных была документирована во многих организациях разработчики создают документы, например, файлы Word или Visio с описаниями объектов, бизнес-процессов или алгоритмов работы процедур. Но при создании таких документов требуется поддерживать их в актуальном состоянии, продумывать параллельный доступ к ним нескольких разработчиков, заботиться об их сохранности. Предлагаю использовать для документирования баз данных механизм расширенных свойств. Для объекта базы данных можно добавить одно или несколько расширенных свойств, в которых может содержаться описание объекта, алгоритм его работы. Данные сведения можно извлекать запросами, они входят как в полный так и в скриптовой бэкап, что делает подобное документирование очень удобным и независимым.
Для изучения расширенных свойств создадим базу данных:
Добавим описание базы данных:
exec sp_addextendedproperty
@name = N'Назначение базы данных',
@value = N'База данных предназначена для осуществления ежедневных банковских транзакций.'
Создадим схему и таблицу:
go
Добавим описания объектов:
Добавим описания столбцов таблицы:
Здесь в качестве имени свойства мы использовали встроенное свойство MS_Description. Благодаря этому мы сможем автоматически увидеть эти описания при создании диаграммы базы данных в Management Studio.
Для полноты картины создадим хранимую процедуру:
go
Для нее мы добавим уже 2 свойства: назначение процедуры и подробное описание алгоритма:
Итак, описания добавлены. Как их теперь просматривать? Можно выполнять запрос к представлению sys.extended_properties:
Можно написать общий запрос, который будет дополнительно извлекать по полю major_id имена таблиц, процедур, схем, а по полю minor_id имена столбцов:
Этот запрос удобен тем, что можно добавить условие where и получить свойства только определенных объектов или столбцов. Лучше всего инкапсулировать этот код в представление.
Кроме запуска sql-кода можно посмотреть и изменить значения расширенных свойств через Management Studio, надо только в обозревателе объектов, выбрать объект, щелкнуть на нем правой кнопкой мыши и в форме свойств перейти на вкладку Расширенные свойства. В скриншоте ниже показана форма свойств для хранимой процедуры AccData.AccountGet:
Если значение свойства нужно изменить, то сначала его требуется удалить с помощью процедуры sp_dropextendedproperty, а затем создать его заново. Если мы теперь создадим диаграмму базы данных, то при включении в нее таблицы, для столбцов которой прописаны свойства, свойства будут автоматически отображены на диаграмме напротив столбца:
Мы можем добавлять свойства к самым различным объектам, а также к файловым группам и ролям.
Для того. чтобы база данных была документирована во многих организациях разработчики создают документы, например, файлы Word или Visio с описаниями объектов, бизнес-процессов или алгоритмов работы процедур. Но при создании таких документов требуется поддерживать их в актуальном состоянии, продумывать параллельный доступ к ним нескольких разработчиков, заботиться об их сохранности. Предлагаю использовать для документирования баз данных механизм расширенных свойств. Для объекта базы данных можно добавить одно или несколько расширенных свойств, в которых может содержаться описание объекта, алгоритм его работы. Данные сведения можно извлекать запросами, они входят как в полный так и в скриптовой бэкап, что делает подобное документирование очень удобным и независимым.
Для изучения расширенных свойств создадим базу данных:
use master
go
create database BankTransactions
go
use BankTransactions
goДобавим описание базы данных:
exec sp_addextendedproperty
@name = N'Назначение базы данных',
@value = N'База данных предназначена для осуществления ежедневных банковских транзакций.'
Создадим схему и таблицу:
create schema AccData
authorization dbo
go
create table AccData.Accounts
(
AccountId int not null,
AccountNumber char ( 20 ) not null,
constraint PK_Accounts_AccountId
primary key clustered ( AccountId asc ) on [PRIMARY],
constraint AK_Accounts_AccountNumber
unique nonclustered ( AccountNumber asc ) on [PRIMARY]
) on [PRIMARY]go
Добавим описания объектов:
exec sp_addextendedproperty
@name = N'Описание схемы', @value = N'Схема предназначена для хранения
объектов,
связанных со счетами, бухгалтерским учетом.',
@level0type = N'SCHEMA', @level0name = N'AccData'
exec sp_addextendedproperty
@name = N'Описание таблицы',
@value = N'Таблица хранит перечень лицевых
номеров счетов',
@level0type = N'SCHEMA', @level0name = N'AccData',
@level1type = N'TABLE', @level1name = N'Accounts'Добавим описания столбцов таблицы:
exec sp_addextendedproperty
@name = N'MS_Description', @value = N'Идентификатор лицевого счета.',
@level0type = N'Schema', @level0name = N'AccData',
@level1type = N'Table', @level1name = N'Accounts',
@level2type = N'Column', @level2name = N'AccountId'
exec sp_addextendedproperty
@name = N'MS_Description', @value = N'Номер лицевого счета.',
@level0type = N'Schema', @level0name = N'AccData',
@level1type = N'Table', @level1name = N'Accounts',
@level2type = N'Column', @level2name = N'AccountNumber'Здесь в качестве имени свойства мы использовали встроенное свойство MS_Description. Благодаря этому мы сможем автоматически увидеть эти описания при создании диаграммы базы данных в Management Studio.
Для полноты картины создадим хранимую процедуру:
create proc AccData.AccountGet
as
begin
set nocount, xact_abort on
select AccountId, AccountNumber
from AccData.Accounts
endgo
Для нее мы добавим уже 2 свойства: назначение процедуры и подробное описание алгоритма:
exec sp_addextendedproperty
@name =
N'Описание',
@value = N'Доступ к счетам',
@level0type = N'schema',
@level0name = N'AccData',
@level1type = N'procedure',
@level1name = N'AccountGet'
exec sp_addextendedproperty
@name =
N'Алгоритм',
@value = N'Процедура выводит перечень лицевых счетов, делая запрос к таблице
Accounts.',
@level0type = N'schema',
@level0name = N'AccData',
@level1type = N'procedure',
@level1name = N'AccountGet'Итак, описания добавлены. Как их теперь просматривать? Можно выполнять запрос к представлению sys.extended_properties:
select class_desc, minor_id, name, value
from sys.extended_properties
where major_id in ( object_id ( N'AccData.Accounts', N'U' ), object_id ( N'AccData.AccountGet', N'P' ), schema_id ( N'AccData' ) )
or class_desc in ( N'DATABASE' )
Можно написать общий запрос, который будет дополнительно извлекать по полю major_id имена таблиц, процедур, схем, а по полю minor_id имена столбцов:
; with Data
as
(
select
case
when sch.name is not null then N'схема'
when obj.name is not null and col.name is null then obj.type_desc collate Cyrillic_General_CI_AS
when obj.name is not null and col.name is not null then N'столбец таблицы '
+
quotename ( object_schema_name ( obj.object_id ) ) + N'.' + quotename ( obj.name )
when prop.class_desc = N'DATABASE' then N'база данных'
end [Тип объекта],
case
when sch.name is not null then sch.name
when obj.name is not null and col.name is null then
quotename ( object_schema_name ( obj.object_id ) ) + N'.' + quotename ( obj.name )
when obj.name is not null and col.name is not null then col.name
when prop.class_desc = N'DATABASE' then db_name ()
end [Имя объекта],
case when prop.name = N'MS_Description' then
N'Описание' else
prop.name end Свойство,
prop.value Значение
from
sys.extended_properties prop
left outer join
sys.objects obj on prop.class_desc in ( N'OBJECT_OR_COLUMN' ) and prop.major_id = obj.[object_id]
left outer join
sys.columns col on obj.object_id = col.object_id and prop.minor_id = col.column_id
left outer join
sys.schemas sch on prop.class_desc in ( N'SCHEMA' ) and prop.major_id = sch.schema_id
)
select [Тип объекта], [Имя объекта], Свойство, Значение
from Data
Этот запрос удобен тем, что можно добавить условие where и получить свойства только определенных объектов или столбцов. Лучше всего инкапсулировать этот код в представление.
Кроме запуска sql-кода можно посмотреть и изменить значения расширенных свойств через Management Studio, надо только в обозревателе объектов, выбрать объект, щелкнуть на нем правой кнопкой мыши и в форме свойств перейти на вкладку Расширенные свойства. В скриншоте ниже показана форма свойств для хранимой процедуры AccData.AccountGet:
Если значение свойства нужно изменить, то сначала его требуется удалить с помощью процедуры sp_dropextendedproperty, а затем создать его заново. Если мы теперь создадим диаграмму базы данных, то при включении в нее таблицы, для столбцов которой прописаны свойства, свойства будут автоматически отображены на диаграмме напротив столбца:
Мы можем добавлять свойства к самым различным объектам, а также к файловым группам и ролям.
