| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
|
|
Прямой доступ Если ни один способ легального доступа к метаданным, хранящимся в системных таблицах, не помогает, остается попробовать напрямую обратиться к этим таблицам. В предыдущих статьях были рассмотрены способы извлечения метаданных из системных таблиц с помощью инструментария, поставляемого корпорацией Microsoft : системных хранимых процедур и системных функций, представлений INFORMATION_SCHEMA и функций свойств. Большую часть необходимой информации можно получить из системных таблиц предложенными способами. Но не всю нужную информацию можно извлечь с помощью этих средств. Поэтому иногда придется прибегнуть к прямому доступу к системным таблицам. (Отметим, что число ситуаций, когда приходится применять прямой доступ к системным таблицам в SQL Server 7.0 значительно меньше, чем в более ранних версиях.) Новые трюки Чтобы привести пример ситуации, когда необходимо задействовать прямой доступ к системным таблицам, рассмотрим дополнительные возможности, введенные Microsoft в SQL Server 7.0. В более ранних версиях не было средств, с помощью которых можно было посмотреть, присоединены ли к таблице какие-либо триггеры . Получить список триггеров, подключенных к определенной таблице, бывает очень полезно, особенно в тех случаях, когда приложение начинает вести себя странно. Но как это сделать , если вы работаете не на SQL Server 7.0? В официальных учебных материалах Microsoft ошибочно указано, что в версии SQL Server 6.5 для этого следует запустить процедуру sp_depends. Однако данная процедура показывает только, какие процедуры или триггеры ссылаются на указанную таблицу в теле кода. Но во многих случаях триггеры ссылаются на таблицу, к которой они относятся, только в обороте ON, а не в теле триггера, следующем за ключевым словом AS. В SQL Server 7.0 в состав поставляемых Microsoft процедур была включена процедура sp_helptrigger. Но если вы все еще работаете на одной из предыдущих версий SQL Server, вам поможет пример создания такой процедуры, приведенный в листинге 1. Системная таблица Sysobjects хранит информацию о том, какие триггеры существуют для каждой таблицы. Идентификатор триггера может находиться в одном из трех столбцов этой таблицы – deltrig, instrig и upgtrig. Эти столбцы содержат осмысленную информацию только для тех строк, которые описывают таблицы или представления. Наличие в этом столбце нуля означает отсутствие триггера соответствующего типа для данной таблицы или представления. Отличное от нуля значение является идентификатором триггера указанного типа. Имя триггера можно получить с помощью системной функции object_name(). Если строка в таблице Sysobjects описывает триггер, то в столбце deltrig находится название таблицы, к которой он относится независимо от типа триггера. Столбцы instrig и upgtrig не используются. (В SQL Server 7.0 в эту таблицу добавлен новый столбец parent_obj. Для триггеров в нем также помещается идентификатор базовой таблицы.) Другой распространенной операцией, не поддерживавшейся более ранними версиями, является изменение владельца объекта. В SQL Server 7.0 появилась новая процедура sp_changeobjectowner. В предыдущих выпусках SQL Server для решения проблемы изменения владельцев при уходе сотрудника из компании рекомендовалось делать владельцем всех объектов Database Owner (DBO). Конечно, такое решение неприемлемо в тех случаях, когда объект уже был создан, поэтому многим системным администраторам приходилось самим писать процедуры, позволявшие менять владельцев объектов. Напомним, что прежде чем создавать процедуру, меняющую владельца, необходимо установить такую конфигурацию, при которой разрешен прямой доступ к системным таблицам. После введения такой возможности можно изменить владельца объекта, заменив значения в столбце uid. (Uid представляет собой идентификатор пользователя, являющегося владельцем объекта.) Если в вашей процедуре предусмотрены два параметра, @new_user_name и @object_name, то основной текст кода будет выглядеть приблизительно следующим образом:
При модификации системных таблиц следует быть особенно внимательным. Если кто-то попытается получить доступ к таблице в то время, как вы меняете ее владельца, можно получить непредсказуемый результат. Прежде чем приступить к рассказу о написании процедур доступа к системным таблицам, имеет смысл проанализировать, как работают некоторые существующие процедуры. Поставляемые системные процедуры написаны на T-SQL. Такой код можно просмотреть двумя способами (если, конечно, процедура не является расширенной). Во-первых, из базы данных Master можно запустить процедуру sp_helptext для получения определения любой процедуры. Кроме того, в подкаталоге \install каталога, где установлен SQL Server, находится текстовый файл procsyst.sql, содержащий определения всех системных процедур. Прародительница кодификаторов Читая определения системных процедур, можно обратить внимание на то, что очень часто упоминается таблица spt_values. Она не является системной, но поставляется вместе с SQL Server. Это большая справочная таблица. Основная часть информации системных таблиц хранится в виде закодированных значений, либо помещенных в отдельное поле, либо как часть цифрового значения. Процедура, интерпретирующая эти коды и переводящая их на относительно вразумительный английский язык, формирует результат, подсоединяя таблицу spt_values к рассматриваемой системной таблице с закодированными значениями. К примеру, процедура sp_dboption формирует отчет о том, какие опции установлены для базы данных. Эта информация хранится в трех столбцах таблицы sysdatabases: status, status2 и category. Наличие 1 в четвертом бите столбца status означает, например, что установлена опция SELECT/BULKCOPY. Код процедуры sp_dboption сначала должен проверить, находится ли в этом бите 1, а затем найти соответствующую расшифровку в таблице spt_values. Процедура не может просто проверить, равно ли значение sysdatabases.status четырем, поскольку в других битах также могут быть 1. Можно использовать битовую арифметику. Битовым эквивалентом AND является амперсант (&). Следующее условие будет истинным, если четвертый бит равен 1, независимо от значений других битов:
Столбец type в таблице spt_values обозначает тип представленной информации. В документации информация об этом отсутствует, но нетрудно вычислить значения большинства типов, анализируя таблицу. К примеру, тип С соответствует значению конфигурации (configuration), L означает режим блокировки (locking), DBR показывает разрешение на роль в базе данных (database role). Применительно к информации в таблице sysdatabases, D указывает на опцию, хранимую в столбце status; D2 означает величину, помещенную в столбец status2, а DC соответствует значению, находящемуся в столбце category, который показывает различные опции репликации для базы данных. В листинге 2 приведен фрагмент процедуры sp_dboption, который формирует список всех опций, установленных для базы данных. Процедура sp_helpdb перечисляет некоторые (но не все) опции, установленные для базы данных. Если проверить код T-SQL процедуры sp_helpdb, выяснится, что она заглядывает только в столбец status, игнорируя столбцы status2 и category. Чтобы определять, какие опции установлены для базы данных, предпочтительнее использовать процедуру sp_dboption. Новая процедура Теперь рассмотрим некоторые задачи, для выполнения которых необходимо обратиться к системным таблицам. Эти задачи невозможно выполнить с помощью тех методов, о которых рассказывалось в предыдущих статьях (см. номера 2 и 3 нашего журнала). SQL Server позволяет вводить в таблицы вычисляемые столбцы. Определение столбца базируется на формуле, по которой значение вычисляется заново каждый раз, когда какой-либо запрос обращается к этому столбцу. К примеру, в базе данных Pubs таблица titles содержит сведения о цене и количестве проданных экземпляров каждой книги. Если понадобится сохранить эту информацию в отдельной таблице вместе с информацией о суммарной выручке от продажи книг, это можно сделать с помощью кода, приведенного в листинге 3. Теперь при проведении выборки из таблицы priceinfo значения в столбце gross вычисляются как произведение соответствующих значений, содержащихся в столбцах price и sales. Запуск хранимой процедуры sp_help для таблицы priceinfo покажет, что столбец gross является вычисляемым, однако по какой формуле производятся вычисления узнать нельзя. Определения вычисляемых столбцов хранятся в таблице syscomments. В столбце ID этой таблицы хранятся идентификаторы объектов, для которых вычисляются значения. Столбец number обычно содержит ссылку на хранимую процедуру или триггер, но если объект является таблицей, то в этом столбце находится идентификатор столбца, значения которого вычисляются. Таблица syscolumns включает названия и идентификаторы всех имеющихся столбцов, а также столбец ID, куда помещается идентификатор таблицы, частью которой является столбец. Значение столбца IsComputed таблицы syscolumns равно 1 для вычисляемых столбцов и 0 для всех остальных. Следующий код позволяет просмотреть определения всех вычисляемых столбцов в базе данных:
Запуск этого запроса показывает, что столбец syscolumns.IsComputed также является вычисляемым. Его формула показывает, что SQL Server вычисляет значение для этого столбца, анализируя бит в столбце syscolumns.colstat:
Это выражение возвратит 1, если четвертый бит столбца colstat равен 1. Во всех остальных случаях результатом вычисления данного выражения будет 0. Предложенный код для просмотра формул вычисляемых столбцов можно оформить в виде хранимой процедуры, параметрами которой будут названия таблицы и столбца. Такая процедура будет выглядеть примерно так, как показано в листинге 4. Но не обольщайтесь: это только начало. Чтобы процедура приобрела вполне законченный вид, в нее необходимо ввести целый ряд проверок. Обновление системных таблиц В какой-то момент понадобится обновить информацию, хранящуюся в системных таблицах. Одна из слушательниц курса моих лекций захотела изменить логическое имя файла базы данных, поскольку ей приходилось часто делать множество копий баз данных. В тех случаях, когда производится создание резервной копии базы данных и ее последующее восстановление, или когда применяются процедуры sp_detach_db, а затем sp_attach_db, появляется возможность переместить физические файлы и изменить их имена. Но логические имена при этом остаются неизменными. При полном изменении имени базы данных логично соответствующим образом поменять и имя файла. В SQL Server Books Online (BOL) не объясняется, как это сделать. Системная таблица sysfiles, в которой находятся имена файлов, реально не существует , это виртуальная таблица. Но недокументируемая таблица sysfiles1 существует на самом деле. Я попробовала внести в нее изменения, заменив название таблицы базы данных напрямую в столбце name таблицы sysfiles1. Похоже, что данный подход приводит к успешному результату. Для такого изменения был использован запрос примерно следующего вида:
При запуске процедуры sp_helpfile возвращается новое имя файла, как и при остановке и старте SQL Server. При создании резервной копии базы данных и ее загрузке система также показывает новое имя файла (конечно, если только восстанавливается не старая копия базы данных, которую делали до смены имени в таблицу sysfiles1). Но в производственной среде я еще не протестировала предлагаемый подход. Безусловно, внесение изменений напрямую в системные таблицы не поддерживается, так что необходимо предварительно обеспечить конфигурацию allow updates. И тем не менее, при необходимости заменить логическое имя файла можно прибегнуть к этому способу. К сожалению, информация о содержимом системных таблиц нигде не документируется. Ее можно получить только путем скрупулезного изучения текстов системных процедур и внимательного анализа процесса поиска информации по запросам системными процедурами. Кэлен Дилани (, ) имеет сертификаты MCT и MCSE, работает независимым консультантом и преподавателем на северо-западе тихоокеанского побережья США. С SQL Server сотрудничает с 1987 года. Кэлен написала книгу «Inside SQL Server 7.0", выпущенную издательством Microsoft Press; она также является соавтором книг «SQL Server 6.5 Unleashed" и «Teach yourself SQL Server in 21 days", изданных в Sams Publishing. |
|
|
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|