Полезные запросы к системным таблицам Firebird и InterBase

Кузьменко Дмитрий, iBase.ru

В этом документе приведены полезные для разработчика или администратора БД запросы к системным таблицам InterBase/Firebird. Для понимания связей между системными таблицами и назначения самих системных таблиц следует прочитать Language Reference (Руководство по языку), приложение System Tables (Системные таблицы). Далее, если вы работаете с PowerDesigner DataArchitect 6.x, стоит взять физическую модель системных таблиц (12K), и тогда вы сами сможете строить подобные запросы буквально за полминуты.
 

Содержание

1. Получение списка полей и их типов по таблицам
2. Мониторинг счетчика метаданных таблиц
3. Скрипт для деактивации всех индексов
4. Получение списка таблиц, связанных по master-detail
5. Получение списка таблиц и полей, которые ссылаются сами на себя
6. Копирование привилегий одного пользователя другому
7. Получение списка таблиц, отсортированного по объему записей
8. Как узнать количество записей в таблицах?
9. Получить список таблиц с индексами и fk, и куда и какие fk ссылаются
10. Список grant, выданных на отсутствующие объекты
11. Список чарсетов и коллэйтов (character sets & collate) для всех столбцов таблиц базы данных
12. Подсчет размера столбцов блобов во всех таблицах
13. Получение списка таблиц, не имеющих ограничений Primary key или Unique

Все приведенные ниже запросы можно оформить в виде view в БД, если вам необходимо часто их использовать. По возможности элементы запроса выделяются цветом (например. таблица1 и ее поля – одним цветом, а таблица2 и ее поля – другим, ключевые слова – жирным шрифтом и т. д.)
 

1. Получение списка полей и их типов по таблицам

select R.RDB$RELATION_NAME, R.RDB$FIELD_POSITION, R.RDB$FIELD_NAME,
F.RDB$FIELD_LENGTH, F.RDB$FIELD_TYPE, F.RDB$FIELD_SCALE, F.RDB$FIELD_SUB_TYPE
from RDB$FIELDS F, RDB$RELATION_FIELDS R
where F.RDB$FIELD_NAME = R.RDB$FIELD_SOURCE and R.RDB$SYSTEM_FLAG = 0
order by R.RDB$RELATION_NAME, R.RDB$FIELD_POSITION

Если вам не нравится нумерация полей с 0, то можно вместо R.RDB$FIELD_POSITION написать R.RDB$FIELD_POSITION+1. Для получения списка полей конкретной таблицы нужно добавить соответствующее условие к where – and RDB$RELATION_NAME = 'MYTABLE'.
 

2. Мониторинг счетчика метаданных таблиц

Известно, что IB позволяет измененять структуру таблицы "на ходу". При этом для правильного считывания структур записей созданными в разных версиях таблицы используется счетчик изменения метаданных. Этот счетчик находится в таблицах RDB$RELATIONS и RDB$FORMATS и хранится в поле RDB$FORMAT. Изменения тела триггеров их их параметров (например ACTIVE/INACTIVE) также приводят к увеличению счетчика метаданных соответствующей таблицы. Однако счетчик этот ограничен максимальным значением 256. Как только он достигнет максимального значения, станет невозможным менять структуры таблиц или триггеры пока не будет сделан backup/restore. Для того, чтобы узнать, насколько близко ваша БД подошла к этому состоянию, можно выдать запрос:

select R.RDB$RELATION_NAME, max(F.RDB$FORMAT)
from RDB$RELATIONS R, RDB$FORMATS F
where R.RDB$RELATION_ID = F.RDB$RELATION_ID and R.RDB$SYSTEM_FLAG = 0
group by R.RDB$RELATION_NAME
having max(F.RDB$FORMAT) > 1
order by 2 desc

Здесь выбирается максимальный достигнутый на текущий момент номер версии метаданных для каждой несистемной таблицы. При помощи having max(F.RDB$FORMAT) > x можно управлять "порогом" версий метаданных, которые вы хотите видеть, если количество таблиц в базе данных слишком большое. Конструкция order by 2 desc сортирует записи, помещая самый большой номер версии метаданных (max(f.rdb$format)) в начало таблицы.
 

3. Если вам пришла в голову идея перестроить все индексы в базе данных,

то для этих целей очень легко получить скрипт. Выполните в WISQL следующий запрос:

select 'alter index ' || RDB$INDEX_NAME || ' inactive;'
from RDB$INDICES
where RDB$INDEX_NAME not like 'RDB$%';

И из области вывода WISQL можно скопировать готовый текст. Получить скрипт для пересоздания индексов можно выполнив тот же запрос, заменив слово 'inactive' на 'active'. Условие where ... not like 'rdb$%' исключает из результата имена индексов, которые строятся автоматически по первичным и вторичным ключам – такие индексы могут быть удалены только при удалении соответствующего constraint.
 

4. Получение списка таблиц базы данных, связанных по master-detail,

т. е. посредством foreign key:

select F.RDB$RELATION_NAME, F.RDB$CONSTRAINT_NAME,
       T.RDB$RELATION_NAME, T.RDB$CONSTRAINT_NAME
from RDB$REF_CONSTRAINTS C, RDB$RELATION_CONSTRAINTS F, RDB$RELATION_CONSTRAINTS T
where C.RDB$CONSTRAINT_NAME = F.RDB$CONSTRAINT_NAME and
      T.RDB$CONSTRAINT_NAME = C.RDB$CONST_NAME_UQ
order by F.RDB$RELATION_NAME, T.RDB$RELATION_NAME

Смысл этого запроса следующий: таблица RDB$RELATION_CONSTRAINTS содержит все имена constraints, включая первичные и вторичные ключи. Таблица RDB$REF_CONSTRAINTS содержит список всех вторичных ключей. Задача – вытащить имена таблиц и имена ссылающихся на них таблиц, при помощи двукратного обращения к RDB$RELATION_CONSTRAINTS (алиасы F и T, from и to соответственно). В результате получаем четыре столбца:

• F.RDB$RELATION_NAME   - имя таблицы, которая ссылается на другую при помощи foreign key
• F.RDB$CONSTRAINT_NAME - имя соответствующего foreign key
• T.RDB$RELATION_NAME   - имя таблицы, на которую ссылаются (используется как справочник)
• F.RDB$CONSTRAINT_NAME - имя primary key constraint таблицы T.RDB$RELATION_NAME

Второй и четвертый столбцы чисто информативные. Order by – для красоты. Запрос такого типа можно использовать как для организации механизма репликации, так и для создания программ копирования данных из одной базы данных в другую.

Поскольку в случае копирования данных из одной БД в другую требуется сначала копировать таблицы, которые являются справочными (т.е. не имеют ссылок по foreign key на другие таблицы), то получение списка таких таблиц может также оказаться полезным:

select distinct C.RDB$RELATION_NAME
from RDB$RELATION_CONSTRAINTS C
where not exists (select * from RDB$RELATION_CONSTRAINTS R
                  where R.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY' and
                        R.RDB$RELATION_NAME = C.RDB$RELATION_NAME)
order by C.RDB$RELATION_NAME

Здесь из результата выборки исключаются таблицы, которые имеют хотя-бы одну связь по foreign key. Distinct используется для исключения повторения имен таблиц, т. к. у таблицы может быть несколько constraints, которые совсем необязательно foreign key. Кому-то этот запрос может показаться не совсем красивым, но он работает так, как нужно. Желающие могут поупражняться в SQL самостоятельно.
 

5. Получение списка таблиц и полей, которые ссылаются сами на себя

Предыдущий запрос вряд ли учитывает ситуацию, когда таблица имеет ссылки по foreign key на саму себя. При этом данные из такой таблицы откопировать можно только либо предварительно удалив аналогичный FK в таблице назначения, либо определенным образом отсортировать считываемые данные. Для этого нужно найти таблицы, ссылающиеся сами на себя, и их поля.

Смысл следующий. Таблица REF_CONSTRAINTS хранит информацию о том, какой FK ссылается на какой PK. Следовательно, сделав пару алиасов RELATION_CONSTRAINTS – RC1 и RC2 – можно вытащить данные об одинаковых RELATION_NAME для PK и FK. Далее, чтобы извлечь имена полей, приходится обращаться к таблице INDEX_SEGMENTS (т. к. RC не хранит информацию о полях связи, а только индекс). Но и чтобы не попасть на одинаковые имена полей для разных таблиц, приходится сравнивать имя таблицы поля с именем таблицы RC1.

Разумеется, если ссылающихся на самих себя FK у таблицы более одного, то сложность задачи возрастает намного. И дело здесь даже не в порядке полей, а в том, какие именно значения в них записаны. Теоретически может потребоваться открыть столько запросов, сколько таких FK у таблицы, каждый отсортированный по своему полю. И дальше пытаться по очереди копировать записи из всех запросов, контролируя отсутствие дубликатов (или exceptions) по первичному ключу.
 

6. Копирование привилегий доступа

от одного пользователя к другому. Такое бывает необходимо, если нужно сменить USERNAME для пользователя. Процедуру предложил Rado Benc. Если необходимость в такой операции возникает только один раз, то разумеется, можно воспользоваться просто оператором insert into ... select from, вырезанным из этой процедуры. Ну и конечно, перед выполнением процедуры или запроса, не забудьте сделать backup и extract metadata for database в WISQL. Осторожность не помешает.

create procedure COPY_USER
(OLDUSER VARCHAR(32), NEWUSER VARCHAR(32))
as
begin
  /* Be aware! Inappropriate use of this procedure */
  /* may cause database crash and permanent data loss :) */
  if (OLDUSER <> NEWUSER) then
    insert into RDB$USER_PRIVILEGES (
      RDB$USER,
      RDB$GRANTOR,
      RDB$PRIVILEGE,
      RDB$GRANT_OPTION,
      RDB$RELATION_NAME,
      RDB$FIELD_NAME,
      RDB$USER_TYPE,
      RDB$OBJECT_TYPE)
    select
      :NEWUSER,
      RDB$GRANTOR,
      RDB$PRIVILEGE,
      RDB$GRANT_OPTION,
      RDB$RELATION_NAME,
      RDB$FIELD_NAME,
      RDB$USER_TYPE,
      RDB$OBJECT_TYPE
    from RDB$USER_PRIVILEGES
      where (RDB$USER = :OLDUSER);
end

Если после копирования привилегий вам "старый" пользователь больше не нужен, то вы можете удалить его привилегии командой

delete from RDB$USER_PRIVILEGES
where RDB$USER = "your old user"
 

7. Получение списка таблиц, отсортированного по количеству записей

Известно, что оптимизатор для планирования запросов использует информацию об уникальности конкретного индекса. Эта информация хранится в столбце RDB$STATISTICS таблицы RDB$INDICES. Обновляется эта статистика или при перестройке (создании) индекса, или при выполнении оператора

SET STATISTICS INDEX

Если вы пользуетесь утилитой IBExpert (или подобной), то в меню алиаса есть пункт Recompute index selectivity, который пересчитывает статистику для всех индексов. Также можно воспользоваться утилитой gidx из комплекта gtools.

После сбора статистики запрос

select rdb$relation_name,
cast(rdb$statistics as numeric(15,13)) from rdb$indices
where rdb$index_name starting with 'RDB$PRI'
order by rdb$statistics

и выдаст список имен таблиц в порядке убывания по относительному числу записей.
 

8. Как узнать количество записей в таблицах?

Конечно, обычный способ это select count(*) from table, но этот способ может быть долгим.

Более простой способ – это использовать статистику по уникальным индексам. Статистика хранится в RDB$INDICES.RDB$INDEX_STATISTICS и представляет собой результат выполнения формулы 1/(Keys-Duplicates), где Keys – число ключей индекса (равно или больше числа записей), а Duplicates – количество повторяющихся значений. Для уникального индекса Duplicates будет всегда равно 0. Поэтому если 1 поделить на результат вычисления этой формулы, мы получим количество записей в конкретной таблице с достаточно высокой точностью:

Последнее условие необходимо для исключения таблиц с числом записей = 0. В результат выборки попадут только те таблицы, которые имеют первичный ключ объявленнный как PRIMARY KEY. Если вы вместо PK используете UNIQUE – поменяйте запрос.
 

9. Получить список таблиц с индексами и fk, и куда и какие fk ссылаются

Данный запрос выбирает: индекс, таблицу-detail, constraint FK, по которому создан индекс, таблицу-master, constraint PK и индекс, куда ссылается исходный FK-constraint. Идея этого запроса возникла для отслеживания таблиц с поврежденными связями master-detail после ремонта баз данных (ситуация описана здесь).

В запросе для поиска неактивных индексов FK нужно заменить условие i.rdb$index_inactive = 0 на i.rdb$index_inactive = 1. То есть, запрос будет показывать информацию detail-master для таблиц, у которых неактивен FK из-за того, что в detail-таблице есть записи, ссылающиеся на отсутствующие записи в таблице master.

10. Получить список grant, выданных на отсутствующие объекты

В серверах IB/FB присутствует ошибка, при которой в случае удаления (drop) нескольких объектов в одной транзакции могут остатся grant в rdb$user_privileges. Такая же проблема может быть и из-за повреждения системных таблиц. Для того, чтобы обнаружить такие grant, можно выполнить запрос:

Найденные записи можно безболезненно удалить прямо из rdb$user_privileges.
 

11. Список чарсетов и коллэйтов (character sets & collate) для всех столбцов таблиц базы данных

Иногда бывает нужно посмотреть все чарсеты у строковых полей таблиц (как минимум) и их collate, для обнаружения некорректно заданных, например.

Чарсет и коллэйт указаны у столбцов в rdb$fields. Используемые в таблицах столбцы – в rdb$relations. При желании эту таблицу можно заменить на rdb$procedure_parameters для проверки того же самого в отношении входных и выходных параметров процедур. Далее идет связка rdb$fields с таблицами чарсетов и коллэйтов (не забудьте, что rdb$collations – подчиненная для rdb$character_sets таблица).

Отфильтровать только строковые столбцы можно было бы указав проверку на field_type, однако это проще сделать проверив чарсет стольбца на is null – он будет null у всех остальных столбцов, кроме строк и блобов. Также исключаем из проверки все системные таблицы, отфильтровав названия, начинающиеся с 'rdb$'

сортируем результат по имени таблицы и реальному имени столбца. Если хочется увидеть название типа столбца по его коду, то нужно добавить в from таблицу rdb$types t, объединить в where f.rdb$field_type = t.rdb$type and t.rdb$field_name = 'RDB$FIELD_TYPE', и вместо f.rdb$field_name в запросе вывести t.rdb$type_name.
 

12. Подсчет размера столбцов блобов во всех таблицах

С увеличением объемов данных к 2011 году нормальный размер БД это 10-50 гигабайт. Иногда возникают ситуации, когда нужно понять, в каких таблицах блобы занимают больше всего места, т. к. в статистике, выдаваемой gstat -a -r блобы не учитываются (и IBAnalyst поэтому тоже о блобах ничего сказать не может).

Микросекунда, участник форума sql.ru, предложил следующий запрос для Firebird 2.5:

13. Получение списка таблиц, не имеющих ограничений Primary key или Unique

Автор: С. Никитин.