Автоматическое документирование базы данных

В процессе разработки баз данных часто возникает следующая ситуация. В процессе развития базы данных в ней создаются различные объекты: файловые группы, схемы, таблицы, роли. Когда объектов становится много, программист может забыть о назначении некоторых из них, например, таблиц и их столбцов, об особенностях работы процедур. Разработчики, которые работают над одной частью базы данных могут не иметь достаточных знаний об особенностях работы других частей базы, которые были созданы другими разработчиками. Если разработчик создал таблицу, схему или хранимую процедуру, а затем долгое время с ней не работал, то возвращаясь к этим объектам, он не всегда может вспомнить об их назначении и особенностях.
Для того. чтобы база данных была документирована во многих организациях разработчики создают документы, например, файлы 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

end
go

Для нее мы добавим уже 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, а затем создать его заново. Если мы теперь создадим диаграмму базы данных, то при включении в нее таблицы, для столбцов которой прописаны свойства, свойства будут автоматически отображены на диаграмме напротив столбца:

Мы можем добавлять свойства к самым различным объектам, а также к файловым группам и ролям.