Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выборка program units в порядке наличия зависимостей  [new]
WinterGraveyard
Member

Откуда:
Сообщений: 72
Возникла задача сделать выборку program units (функции, ХП, вьюхи)+синонимов с учетом зависимостей объектов - чтобы потом объекты этой выборки отскриптовать в один файл, и применить его, создав все объекты разом. Почему не устраивает встроеннное скриптование SSMS: в базе живет ERP, которая в очень большом количестве создает автогененрируемые ХП (сейчас их в базе ~ 180 тыс.) - они, во-первых, мне не нужны (нужны только объекты от самописной разработки в базе), во-вторых, при таком большом количестве объектов в БД штатное средство SSMS сначала очень надолго задумывается, а потом SSMS вылетает по ошибке нехватки памяти. Поэтому было решено список таких объектов сформировать самостоятельно, и потом каждый объект отскриптовать через SMO. Вторая часть проблем не представляет, но вот первая... Запрос я написал, но интересует - не пропустил ли я чего? Всё ли в нем корректно?
Сервер 2005-й, поэтому анализ зависимостей делается через sys.sql_dependencies, а не через sys.dm_sql_referencing_entities; интересуют только зависимости в пределах базы. Deferred names resulution решил не принимать во внимание.
declare
  @t table (
    id int not null primary key
  );
insert into @t
  select
    object_id
  from sys.objects
  where type in ('FN','SN','IF','V','P','TF')
  and not (
    (type='V' and schema_id=schema_id('dbo') and name like 'V$%')
    or
    (type='P' and schema_id=schema_id('dbo') and name like '[A-Z][A-Z][0-9]%')
    or
    (type='FN' and schema_id=schema_id('dbo') and name like 'S$%')
  );
declare
  @t2 table (
    id int not null,
    level int not null,
    primary key(id, level)
  );
;with cte as (
  select id, 1 level
  from @t e
  where not exists ( -- Объекты, ни от кого не зависящие, и от которых никто не зависит
    select 1
    from sys.sql_dependencies d
    where d.object_id=e.id
  ) and not exists (
    select 1
    from sys.sql_dependencies d
    where d.referenced_major_id=e.id
  )
  union
  select id, 1 level
  from @t e
  where not exists ( -- Объекты, ни от кого не зависящие
    select 1
    from sys.sql_dependencies d
    where d.object_id=e.id
  )
  union
  select id, 1 level
  from @t e
  where not exists ( -- Объекты, не зависящие ни от кого из вышевыбранных объектов
    select d.referenced_major_id
    from sys.sql_dependencies d
    where d.object_id=e.id
    intersect
    select id from @t
  )
  union all
  select
    t.id, e.level+1
  from cte e
  join sys.sql_dependencies d on d.referenced_major_id=e.id
    and d.referenced_major_id<>d.object_id -- исключаем рекурсивные ссылки объектов
  join @t t on t.id=d.object_id
)
insert into @t2
  select distinct id, level from cte option(recompile);

insert into @t2
  select id, 100 from @t t
  where not exists (
    select 1 from @t2 t
    where t.id=t.id
  );
;with cte as (
  select
    id, level, row_number() over(partition by id order by level ) ord
  from @t2
)
select
  id, level
from cte
where ord=1
order by level
option(recompile)
29 фев 16, 07:23    [18875612]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить