Полезные запросы к системным таблицам 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. Подсчет размера столбцов блобов во всех таблицах

Все приведенные ниже запросы можно оформить в виде 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)) в начало таблицы.
 
Примечание. На самом деле максимальный номер счетчика метаданных хранится в RDB$RELATIONS, поэтому можно обойтись и более простым запросом:
select RDB$RELATION_NAME, RDB$FORMAT
from RDB$RELATIONS R
where R.RDB$SYSTEM_FLAG = 0 and RDB$FORMAT > 1
order by RDB$FORMAT desc


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

select F.RDB$FIELD_NAME, RC1.RDB$RELATION_NAME
   from
RDB$RELATION_CONSTRAINTS RC1,
RDB$RELATION_CONSTRAINTS RC2,
RDB$REF_CONSTRAINTS RF,
RDB$INDEX_SEGMENTS I,
RDB$RELATION_FIELDS F
   where
RC1.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY' and
RC2.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY' and
RC1.RDB$CONSTRAINT_NAME = RF.RDB$CONSTRAINT_NAME and
RC2.RDB$CONSTRAINT_NAME = RF.RDB$CONST_NAME_UQ and
RC1.RDB$INDEX_NAME = I.RDB$INDEX_NAME and
RC1.RDB$RELATION_NAME = RC2.RDB$RELATION_NAME and
F.RDB$RELATION_NAME = RC1.RDB$RELATION_NAME and
F.RDB$FIELD_NAME = I.RDB$FIELD_NAME
   order by RC1.RDB$RELATION_NAME, F.RDB$FIELD_POSITION


Смысл следующий. Таблица 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 поделить на результат вычисления этой формулы, мы получим количество записей в конкретной таблице с достаточно высокой точностью:

Внимание! Перед выполнением этого запроса надо собрать статистику по индексам (в т. ч. уникальным) либо в IBExpert (Database/recompute selectivity for all indices), либо утилитой gidx, либо вручную (или скриптом) выполняя команды set statistics index <index_name>
SELECT RDB$INDICES.rdb$relation_name RELATION,
  cast(1/I.RDB$STATISTICS as integer) RECORD_COUNT
  FROM RDB$INDICES I
  JOIN RDB$RELATION_CONSTRAINTS C
  ON (C.RDB$INDEX_NAME = I.RDB$INDEX_NAME)
  AND (C.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY')
  AND (I.RDB$STATISTICS > cast(0 as double precision))


Последнее условие необходимо для исключения таблиц с числом записей = 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.

select
   i.RDB$INDEX_NAME what_index,
   i.RDB$RELATION_NAME what_table,
   r.RDB$CONSTRAINT_NAME what_constraint,
   c.RDB$CONST_NAME_UQ refers_to_constraint,
   r2.RDB$RELATION_NAME refers_to_table,
   r2.RDB$INDEX_NAME refers_to_index
from rdb$indices i, rdb$relation_constraints r,
   rdb$ref_constraints c,
   rdb$relation_constraints r2
where i.rdb$index_inactive = 0 and
           r.RDB$INDEX_NAME = i.RDB$INDEX_NAME and
           r.RDB$CONSTRAINT_NAME = c.RDB$CONSTRAINT_NAME and
           r2.RDB$CONSTRAINT_NAME = c.RDB$CONST_NAME_UQ
order by 2, 1

 

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

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

select *
from rdb$user_privileges up
where up.rdb$user_type = 5
         and not exists ( select 1 from rdb$procedures where rdb$procedure_name = up.rdb$user )
union all
select *
from rdb$user_privileges up
where up.rdb$user_type = 2
         and not exists ( select 1 from rdb$triggers where rdb$trigger_name = up.rdb$user )
union all
select *
from rdb$user_privileges up
where up.rdb$user_type = 1
          and not exists ( select 1 from rdb$view_relations where rdb$view_name = up.rdb$user )

Найденные записи можно безболезненно удалить прямо из 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$'

select rf.rdb$relation_name relation, rf.rdb$field_name table_field,
       f.rdb$field_name field_domain, f.rdb$field_type field_type,
       cs.rdb$character_set_name character_set,
       c.rdb$collation_name collation_name
from
   rdb$fields f, rdb$character_sets cs,
   rdb$collations c, rdb$relation_fields rf
where
   cs.rdb$character_set_id = f.rdb$character_set_id and
   c.rdb$collation_id = f.rdb$collation_id and
   c.rdb$character_set_id = cs.rdb$character_set_id and
   f.rdb$field_name = rf.rdb$field_source
   and f.rdb$character_set_id is not null
   and rf.rdb$relation_name not starting with 'RDB$'
order by 1, 2

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

execute block
returns (
   TABLE_NAME CHAR(31),
   FIELD_NAME CHAR(31),
   FIELD_SIZE bigint,
   SQL varchar(200))
as
begin
   for select T.RDB$RELATION_NAME, R.RDB$FIELD_NAME
      from RDB$RELATIONS T
      join RDB$RELATION_FIELDS R on R.RDB$RELATION_NAME=T.RDB$RELATION_NAME
      join RDB$FIELDS F on F.RDB$FIELD_NAME = R.RDB$FIELD_SOURCE
      where F.RDB$FIELD_TYPE=261
      and T.RDB$RELATION_TYPE=0
      order by 1, 2
      into :TABLE_NAME, :FIELD_NAME
   do
      begin
         SQL='select sum(OCTET_LENGTH(' || TRIM(FIELD_NAME) || ')) from ' || TRIM(TABLE_NAME);
         execute statement SQL into FIELD_SIZE;
         suspend;
      end
end

 
someproc.txt

Подпишитесь на новости Firebird в России

Подписаться