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

Откуда:
Сообщений: 297
Пришлось поменять имена столбцов во многих таблицах (более 100 таблиц).

Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?

Заранее спасибо
11 авг 10, 14:33    [9246954]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
dimidrol.net
Member

Откуда: Москва
Сообщений: 58
SQL не строит автоматически зависимостей какие таблицы используются в той или иной процедуре, функции или триггере... так что руками...
11 авг 10, 14:41    [9247040]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
SELECT @@version


???
11 авг 10, 14:44    [9247054]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
Edkonst2008
Member

Откуда:
Сообщений: 503
Salik
Пришлось поменять имена столбцов во многих таблицах (более 100 таблиц).

Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?

Заранее спасибо


А так не пойдет:
Все относится, если есть структура до исправлений.
1) Сравнить 2 БД. Утилиты на Ваш выбор.
2) Вычислить измененные таблицы
3) Просмотрев зависимости - внести изменения.
?
11 авг 10, 14:49    [9247098]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2793
Скриптуете, например, все процедуры. В полученном скрипте меняете create procedure на alter procedure. Запускаете скрипт. Внизу смотрите на ошибки. Результат можно сохранить в файл и при желании вычленить из него список процедур и т.п..
11 авг 10, 14:51    [9247126]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Salik
Пришлось поменять имена столбцов во многих таблицах (более 100 таблиц).

Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?

Заранее спасибо

+
use [DBName]
go

CREATE TABLE #tempdep (objid int NOT NULL, objtype smallint NOT NULL)
go

BEGIN TRANSACTION
go

INSERT INTO #tempdep 

SELECT
tbl.object_id AS [ID],
3
FROM
sys.tables AS tbl
WHERE
(tbl.name=N'table_name' and SCHEMA_NAME(tbl.schema_id)=N'schema_name')
go
COMMIT TRANSACTION
go

declare @find_referencing_objects int
set @find_referencing_objects = 1
-- parameters:
-- 1. create table #tempdep (objid int NOT NULL, objtype smallint NOT NULL)
--    contains source objects
-- 2. @find_referencing_objects defines ordering
--    1 order for drop
--    0 order for script

declare @must_set_nocount_off bit
set @must_set_nocount_off = 0

IF @@OPTIONS & 512 = 0 
   set @must_set_nocount_off = 1
set nocount on

declare @u int
declare @udf int
declare @v int
declare @sp int
declare @def int
declare @rule int
declare @tr int
declare @uda int
declare @uddt int
declare @xml int
declare @udt int
declare @assm int
declare @part_sch int
declare @part_func int
declare @synonym int

set @u = 3
set @udf = 0
set @v = 2
set @sp = 4
set @def = 6
set @rule = 7
set @tr = 8
set @uda = 11
set @synonym = 12
--above 100 -> not in sys.objects
set @uddt = 101
set @xml = 102
set @udt = 103
set @assm = 1000
set @part_sch = 201
set @part_func = 202


/*
 * Create #t1 as temp object holding areas.  Columns are:
 *  object_id     - temp object id
 *  object_type    - temp object type
 *  relative_id      - parent or child object id
 *  relative_type  - parent or child object type
 *  rank  - NULL means dependencies not yet evaluated, else nonNULL.
 *   soft_link - this row should not be used to compute ordering among objects
 *   object_name - name of the temp object
 *   object_schema - name the temp object's schema (if any)
 *   relative_name - name of the relative object
 *   relative_schema - name of the relative object's schema (if any)
 *   degree - the number of relatives that the object has, will be used for computing the rank
 *   object_key - surrogate key that combines object_id and object_type
 *   relative_key - surrogate key that combines relative_id and relative_type
 */
create table #t1(
   object_id         int         NULL,
   object_type       smallint    NULL,
   relative_id       int         NULL,
   relative_type        smallint    NULL,
   rank        smallint    NULL,
   soft_link      bit      NULL,
   object_name       sysname     NULL,
   object_schema        sysname     NULL,
   relative_name     sysname     NULL,
   relative_schema      sysname     NULL,
   degree            int NULL,
   object_key bigint NULL,
   relative_key bigint NULL
)

create unique clustered index i1 on #t1(object_id, object_type, relative_id, relative_type) with IGNORE_DUP_KEY

declare @iter_no int
set @iter_no = 1

declare @rows int
set @rows = 1

declare @rowcount_ck int
set @rowcount_ck = 0

insert #t1 (relative_id, relative_type, rank) 
   select l.objid, l.objtype, @iter_no from #tempdep l

while @rows > 0
begin
   set @rows = 0
   if( 1 = @find_referencing_objects )
   begin
      --tables that reference uddts or udts (parameters that reference types are in sql_dependencies )
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, c.object_id, @u, @iter_no + 1
         from #t1 as t
         join sys.columns as c on  c.user_type_id = t.relative_id
         join sys.tables as tbl on tbl.object_id = c.object_id -- eliminate views
         where @iter_no = t.rank and (t.relative_type=@uddt OR t.relative_type=@udt)
      set @rows = @rows + @@rowcount

      --tables that reference defaults ( only default objects )
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, clmns.object_id, @u, @iter_no + 1
         from #t1 as t
         join sys.columns as clmns on clmns.default_object_id = t.relative_id
         join sys.objects as o on o.object_id = t.relative_id and 0 = isnull(o.parent_object_id, 0)
         where @iter_no = t.rank and t.relative_type = @def
      set @rows = @rows + @@rowcount

      --types that reference defaults ( only default objects )
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, tp.user_type_id, @uddt, @iter_no + 1
         from #t1 as t
         join sys.types as tp on tp.default_object_id = t.relative_id
         join sys.objects as o on o.object_id = t.relative_id and 0 = isnull(o.parent_object_id, 0)
         where @iter_no = t.rank and t.relative_type = @def
      set @rows = @rows + @@rowcount

      --tables that reference rules
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, clmns.object_id, @u, @iter_no + 1
         from #t1 as t
         join sys.columns as clmns on clmns.rule_object_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @rule
      set @rows = @rows + @@rowcount

      --types that reference rules
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, tp.user_type_id, @uddt, @iter_no + 1
         from #t1 as t
         join sys.types as tp on tp.rule_object_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @rule
      set @rows = @rows + @@rowcount

      --tables that reference XmlSchemaCollections
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, c.object_id, @u, @iter_no + 1
         from #t1 as t
         join sys.columns as c on c.xml_collection_id = t.relative_id
         join sys.tables as tbl on tbl.object_id = c.object_id -- eliminate views
         where @iter_no = t.rank and t.relative_type = @xml
      set @rows = @rows + @@rowcount

      --procedures that reference XmlSchemaCollections
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, c.object_id, case when o.type in ( 'P', 'RF', 'PC' ) then @sp else @udf end, @iter_no + 1
         from #t1 as t
         join sys.parameters as c on c.xml_collection_id = t.relative_id
         join sys.objects as o on o.object_id = c.object_id
         where @iter_no = t.rank and t.relative_type = @xml
      set @rows = @rows + @@rowcount

      --udf, sp, uda, trigger all that reference assembly
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, am.object_id, (case o.type when 'AF' then @uda when 'PC' then @sp when 'FS' then @udf 
when 'FT' then @udf when 'TA' then @tr else @udf end), @iter_no + 1
         from #t1 as t
         join sys.assembly_modules as am on am.assembly_id = t.relative_id
         join sys.objects as o on am.object_id = o.object_id
         where @iter_no = t.rank and t.relative_type = @assm
      set @rows = @rows + @@rowcount

      -- CLR udf, sp, uda that reference udt
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select distinct t.relative_id, 
               t.relative_type, 
               am.object_id, 
               (case o.type 
                  when 'AF' then @uda 
                  when 'PC' then @sp 
                  when 'FS' then @udf 
                  when 'FT' then @udf 
                  when 'TA' then @tr 
                  else @udf end), 
               @iter_no + 1
         from #t1 as t
         join sys.parameters as sp on sp.user_type_id = t.relative_id
         join sys.assembly_modules as am on sp.object_id = am.object_id  
         join sys.objects as o on sp.object_id = o.object_id
         where @iter_no = t.rank and t.relative_type = @udt
      set @rows = @rows + @@rowcount

      --udt that reference assembly
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, at.user_type_id, @udt, @iter_no + 1
         from #t1 as t
         join sys.assembly_types as at on at.assembly_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @assm
      set @rows = @rows + @@rowcount

      --assembly that reference assembly
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, ar.assembly_id, @assm, @iter_no + 1
         from #t1 as t
         join sys.assembly_references as ar on ar.referenced_assembly_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @assm
      set @rows = @rows + @@rowcount

      --table references table
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, fk.parent_object_id, @u, @iter_no + 1
         from #t1 as t
         join sys.foreign_keys as fk on fk.referenced_object_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @u
      set @rows = @rows + @@rowcount

      --table,view references partition scheme
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)      
         select t.relative_id, t.relative_type, idx.object_id, case o.type when 'V' then @v else @u end, @iter_no + 1
         from #t1 as t
         join sys.indexes as idx on idx.data_space_id = t.relative_id 
         join sys.objects as o on o.object_id = idx.object_id
         where @iter_no = t.rank and t.relative_type = @part_sch
      set @rows = @rows + @@rowcount

      --partition scheme references partition function
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)      
         select t.relative_id, t.relative_type, ps.data_space_id, @part_sch, @iter_no + 1
         from #t1 as t
         join sys.partition_schemes as ps on ps.function_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @part_func
      set @rows = @rows + @@rowcount

      --non-schema-bound parameter references type
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, p.object_id, 
               case when obj.type in ( 'P', 'PC' ) then @sp else @udf
               end, @iter_no + 1
         from #t1 as t
         join sys.parameters as p on 
             p.user_type_id = t.relative_id and  t.relative_type in (@uddt, @udt)
         join sys.objects as obj on obj.object_id = p.object_id and obj.type in ( 'P',  'PC', 'TF', 'FN', 'IF', 'FS', 'FT')
         and ISNULL(objectproperty(obj.object_id, 'isschemabound'), 0) = 0
         where @iter_no = t.rank
      set @rows = @rows + @@rowcount

      --view, procedure references table, view, procedure
      --procedure references type
      --table(check) references procedure
      --trigger references table, procedure
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, case when 'C' = obj.type then obj.parent_object_id else dp.object_id end, 
               case when obj.type  in ('U', 'C') then @u when 'V' = obj.type then @v when 'TR' = obj.type then @tr 
               when obj.type in ( 'P', 'RF', 'PC' ) then @sp 
               when obj.type in ( 'TF', 'FN', 'IF', 'FS', 'FT' ) then @udf
               end, @iter_no + 1
         from #t1 as t
         join sys.sql_dependencies as dp on 
            -- reference table, view procedure
            ( class < 2 and dp.referenced_major_id = t.relative_id and t.relative_type in ( @u, @v, @sp, @udf) )
            --reference type
             or ( 2 = class  and dp.referenced_major_id = t.relative_id and  t.relative_type in (@uddt, @udt))
            --reference xml namespace ( not supported by server right now )
            --or ( 3 = class  and dp.referenced_major_id = t.relative_id and @xml = t.relative_type )
         join sys.objects as obj on obj.object_id = dp.object_id and obj.type in ( 'U', 'V', 'P', 'RF', 'PC', 'TR', 'TF', 'FN', 'IF', 
'FS', 'FT', 'C')
         where @iter_no = t.rank
      set @rows = @rows + @@rowcount

   end -- 1 = @find_referencing_objects
   else
   begin -- find referenced objects
      --check references table
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, dp.object_id, 77 /*place holder for check*/, @iter_no
         from #t1 as t
         join sys.sql_dependencies as dp on 
            -- reference table
            class < 2 and dp.referenced_major_id = t.relative_id and t.relative_type = @u
         join sys.objects as obj on obj.object_id = dp.object_id and obj.type  = 'C'
         where @iter_no = t.rank
      set @rowcount_ck = @@rowcount

      --non-schema-bound parameter references type
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select distinct 
            t.relative_id,	-- object_id
            t.relative_type,	-- object_type
            p.user_type_id,	-- relative_id
            case p.system_type_id when 240 then @udt else @uddt end,
            @iter_no + 1
         from #t1 as t
         join sys.parameters as p on
             p.object_id = t.relative_id and p.user_type_id > 256 and t.relative_type in ( @sp, @udf )
             and ISNULL(objectproperty(p.object_id, 'isschemabound'), 0) = 0
         where @iter_no = t.rank
      set @rows = @rows + @@rowcount

      --view, procedure referenced by table, view, procedure
      --type referenced by procedure
      --check referenced by table
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select distinct 
            case when 77 = t.relative_type then obj2.parent_object_id else t.relative_id end,   -- object_id
            case when 77 = t.relative_type then @u else relative_type end,                -- object_type
            dp.referenced_major_id,                                              -- relative_id
            case                                                           -- relative_type
               when dp.class < 2 then 
                  case when 'U' = obj.type then @u 
                  when 'V' = obj.type then @v 
                  when 'TR' = obj.type then @tr 
				  when 'AF' = obj.type then @uda 
                  when obj.type in ( 'P', 'RF', 'PC' ) then @sp 
                  when obj.type in ( 'TF', 'FN', 'IF', 'FS', 'FT' ) then @udf
                  when exists (select * from sys.synonyms syn where syn.object_id = dp.referenced_major_id ) then @synonym
                  end
               when dp.class = 2 then (case 
                                    when exists (select * from sys.assembly_types sat where sat.user_type_id = dp.referenced_major_id) 
then @udt 
                                    else @uddt 
                                 end) 
            end, 
            @iter_no + 1
         from #t1 as t
         join sys.sql_dependencies as dp on 
            -- reference table, view procedure
            ( class < 2 and dp.object_id = t.relative_id and t.relative_type in ( @u, @v, @sp, @udf, @tr, @uda, 77) )
            --reference type
             or ( 2 = class  and dp.object_id = t.relative_id ) -- t.relative_type?
            --reference xml namespace ( not supported by server right now )
            --or ( 3 = class  and dp.referenced_major_id = t.relative_id and @xml = t.relative_type )
         left join sys.objects as obj on obj.object_id = dp.referenced_major_id and dp.class < 2 and obj.type in ( 'U', 'V', 'P', 'RF', 
'PC', 'TF', 'FN', 'IF', 'FS', 'FT', 'TR', 'AF')
         left join sys.objects as obj2 on obj2.object_id = t.relative_id and 77 = t.relative_type
         where @iter_no = t.rank
      set @rows = @rows + @@rowcount

      if @rowcount_ck > 0 
      begin
         delete from #t1 where relative_type = 77
      end
      
      --table or view referenced by trigger
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, tr.parent_id, case o.type when 'V' then @v else @u end, @iter_no + 1
         from #t1 as t
         join sys.triggers as tr on tr.object_id = t.relative_id
		 join sys.objects as o on o.object_id = tr.parent_id
         where @iter_no = t.rank and t.relative_type = @tr
      set @rows = @rows + @@rowcount
      
      --table referenced by table
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, fk.referenced_object_id, @u, @iter_no + 1
         from #t1 as t
         join sys.foreign_keys as fk on fk.parent_object_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @u
      set @rows = @rows + @@rowcount

      --assembly referenced by assembly
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, ar.referenced_assembly_id, @assm, @iter_no + 1
         from #t1 as t
         join sys.assembly_references as ar on ar.assembly_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @assm
      set @rows = @rows + @@rowcount

      --assembly referenced by udt
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, at.assembly_id, @assm, @iter_no + 1
         from #t1 as t
         join sys.assembly_types as at on at.user_type_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @udt
      set @rows = @rows + @@rowcount

      -- assembly referenced by udf, sp, uda, trigger
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, am.assembly_id, @assm, @iter_no + 1
         from #t1 as t
         join sys.assembly_modules as am on am.object_id = t.relative_id
         where @iter_no = t.rank and t.relative_type in ( @udf, @sp, @uda, @tr)
      set @rows = @rows + @@rowcount

      -- udt referenced by CLR udf, sp, uda
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select distinct 
               t.relative_id, 
               t.relative_type, 
               at.user_type_id, 
               @udt, 
               @iter_no + 1
         from #t1 as t
         join sys.parameters as sp on sp.object_id = t.relative_id
         join sys.assembly_modules as am on am.object_id = sp.object_id
         join sys.assembly_types as at on sp.user_type_id = at.user_type_id
         where @iter_no = t.rank and t.relative_type in (@udf, @sp, @uda)
      set @rows = @rows + @@rowcount

      --clr types referenced by tables ( types referenced by parameters are in sql_dependencies )
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, c.user_type_id, @udt, @iter_no + 1
         from #t1 as t
         join sys.columns as c on  c.object_id = t.relative_id
         join sys.assembly_types as tp on tp.user_type_id = c.user_type_id
         where @iter_no = t.rank and t.relative_type = @u
      set @rows = @rows + @@rowcount
      
   end
   set @iter_no = @iter_no + 1
end --main loop

--objects that don't need to be in the loop because they don't reference anybody
if( 0 = @find_referencing_objects )
begin
   --alias types referenced by tables ( types referenced by parameters are in sql_dependencies )
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, c.user_type_id, @uddt, @iter_no + 1
      from #t1 as t
      join sys.columns as c on  c.object_id = t.relative_id
      join sys.types as tp on tp.user_type_id = c.user_type_id and tp.is_user_defined = 1
      where t.relative_type = @u and tp.is_assembly_type = 0

   if @@rowcount > 0 
   begin
      set @iter_no = @iter_no + 1
   end
   
   --defaults referenced by types
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, tp.default_object_id, @def, @iter_no + 1
      from #t1 as t
      join sys.types as tp on tp.user_type_id = t.relative_id and tp.default_object_id > 0
      join sys.objects as o on o.object_id = tp.default_object_id and 0 = isnull(o.parent_object_id, 0)
      where t.relative_type = @uddt

   --defaults referenced by tables( only default objects )
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, clmns.default_object_id, @def, @iter_no + 1
      from #t1 as t
      join sys.columns as clmns on clmns.object_id = t.relative_id
      join sys.objects as o on o.object_id = clmns.default_object_id and 0 = isnull(o.parent_object_id, 0)
      where t.relative_type = @u

   --rules referenced by types
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, tp.rule_object_id, @rule, @iter_no + 1
      from #t1 as t
      join sys.types as tp on tp.user_type_id = t.relative_id and tp.rule_object_id > 0
      where t.relative_type = @uddt

   --rules referenced by tables
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, clmns.rule_object_id, @rule, @iter_no + 1
      from #t1 as t
      join sys.columns as clmns on clmns.object_id = t.relative_id and clmns.rule_object_id > 0
      where t.relative_type = @u

   --XmlSchemaCollections referenced by table
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, c.xml_collection_id, @xml, @iter_no + 1
      from #t1 as t
      join sys.columns as c on c.object_id = t.relative_id and c.xml_collection_id > 0
      where t.relative_type = @u

   --XmlSchemaCollections referenced by procedures
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, c.xml_collection_id, @xml, @iter_no + 1
      from #t1 as t
      join sys.parameters as c on c.object_id = t.relative_id and c.xml_collection_id > 0
      where t.relative_type in ( @sp, @udf)

   --partition scheme referenced by table,view
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)      
      select t.relative_id, t.relative_type, ps.data_space_id, @part_sch, @iter_no + 1
      from #t1 as t
      join sys.indexes as idx on idx.object_id = t.relative_id 
      join sys.partition_schemes as ps on ps.data_space_id = idx.data_space_id
      where t.relative_type in (@u, @v)

   --partition function referenced by partition scheme
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)      
      select t.relative_id, t.relative_type, ps.function_id, @part_func, @iter_no + 1
      from #t1 as t
      join sys.partition_schemes as ps on ps.data_space_id = t.relative_id
      where t.relative_type = @part_sch
      
end

--cleanup circular references
delete #t1 where object_id = relative_id and object_type=relative_type

--allow circular dependencies by cuting one of the branches
--mark as soft links dependencies between tables
-- at script time we will need to take care to script fks and checks separately
update #t1 set soft_link = 1 where ( object_type = @u and relative_type = @u )

--add independent objects first in the list
insert #t1 ( object_id, object_type, rank) 
   select t.relative_id, t.relative_type, 1 from #t1 t where t.relative_id not in ( select t2.object_id from #t1 t2 where not 
t2.object_id is null )

--delete initial objects
delete #t1 where object_id is null

-- compute the surrogate keys to make sorting easier
update #t1 set object_key = object_id + convert(bigint, 0xfFFFFFFF) * object_type
update #t1 set relative_key = relative_id + convert(bigint, 0xfFFFFFFF) * relative_type

create index index_key on #t1 (object_key, relative_key)

update #t1 set rank = 0
-- computing the degree of the nodes
update #t1 set degree = (
      select count(*) 
      from #t1 t_alias 
      where t_alias.object_key = #t1.object_key and 
            t_alias.relative_id is not null and 
            t_alias.soft_link is null)

-- perform topological sorting 
set @iter_no=1
while 1=1
begin 
   update #t1 set rank=@iter_no where degree=0
   -- end the loop if no more rows left to process
   if (@@rowcount=0) break
   update #t1 set degree=NULL where rank = @iter_no
   
   update #t1 set degree = (
      select count(*) 
         from #t1 t_alias 
         where t_alias.object_key = #t1.object_key and 
            t_alias.relative_key is not null and 
            t_alias.relative_key in (select t_alias2.object_key from #t1 t_alias2 where t_alias2.rank=0 and t_alias2.soft_link is null) 
and
            t_alias.rank=0 and t_alias.soft_link is null)
      where degree is not null
      
   set @iter_no=@iter_no+1
end

--add name schema
update #t1 set object_name = o.name, object_schema = schema_name(o.schema_id)
from sys.objects AS o 
where o.object_id = #t1.object_id and object_type in ( @u, @udf, @v, @sp, @def, @rule, @uda)

update #t1 set relative_type = case op.type when 'V' then @v else @u end, object_name = o.name, object_schema = 
schema_name(o.schema_id), relative_name = op.name, relative_schema = schema_name(op.schema_id)
from sys.objects AS o 
join sys.objects AS op on op.object_id = o.parent_object_id
where o.object_id = #t1.object_id and object_type = @tr

update #t1 set object_name = t.name, object_schema = schema_name(t.schema_id)
from sys.types AS t
where t.user_type_id = #t1.object_id and object_type in ( @uddt, @udt )

update #t1 set object_name = x.name, object_schema = schema_name(x.schema_id)
from sys.xml_schema_collections AS x
where x.xml_collection_id = #t1.object_id and object_type = @xml

update #t1 set object_name = p.name, object_schema = null
from sys.partition_schemes AS p
where p.data_space_id = #t1.object_id and object_type = @part_sch


update #t1 set object_name = p.name, object_schema = null
from sys.partition_functions AS p
where p.function_id = #t1.object_id and object_type = @part_func

update #t1 set object_name = a.name, object_schema = null
from sys.assemblies AS a
where a.assembly_id = #t1.object_id and object_type = @assm

update #t1 set object_name = syn.name, object_schema = schema_name(syn.schema_id)
from sys.synonyms AS syn
where syn.object_id = #t1.object_id and object_type = @synonym

-- delete objects for which we could not resolve the table name or schema
-- because we may not have enough privileges
delete from #t1 
where 
	object_name is null or 
	(object_schema is null  and object_type not in (@assm, @part_func, @part_sch))

--final select
select object_id, object_type, relative_id, relative_type, object_name, object_schema, relative_name, relative_schema
 from #t1 
 order by rank, relative_id
 
drop table #t1
drop table #tempdep

IF @must_set_nocount_off > 0 
   set nocount off

go

USE [master]
go

-- trace pause )))
11 авг 10, 14:55    [9247160]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
Salik
Member

Откуда:
Сообщений: 297
sql 2008
11 авг 10, 16:05    [9247800]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
Salik
Member

Откуда:
Сообщений: 297
Prolog,

пробовал
получил громадмое число ошибок
поэтому хотелось бы получить список нелегалных объектов
из какой нибудь вьюшки.
11 авг 10, 16:07    [9247818]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
Salik
Member

Откуда:
Сообщений: 297
pkarklin,

sql 2008 SP1
11 авг 10, 16:07    [9247821]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2793
Salik
Prolog,

пробовал
получил громадмое число ошибок
поэтому хотелось бы получить список нелегалных объектов
из какой нибудь вьюшки.
А если нет такой view, что будем делать?
Зачем нужно было переименовывать такое большое количество столбцов в таблицах. Если уж было позарез необходимо, то не проще бы создать view, в которых и переименовать столбцы?
11 авг 10, 16:14    [9247898]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Salik
pkarklin,

sql 2008 SP1


http://msdn.microsoft.com/en-us/library/cc879246.aspx

И раздел See Also.
11 авг 10, 16:17    [9247918]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
Salik
Member

Откуда:
Сообщений: 297
Senya_L,

А чего этот сцрипт делает ?
запустил он ничего не находит.
11 авг 10, 16:51    [9248248]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4538
SQL Search пока халява
11 авг 10, 17:33    [9248615]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Salik
Senya_L,

А чего этот сцрипт делает ?
запустил он ничего не находит.
"Сцрипт" выводит зависимые объекты. :)
1. В первой строке скрипта измените DBName => название свой БД.
2. В строке
(tbl.name=N'table_name' and SCHEMA_NAME(tbl.schema_id)=N'schema_name')
Подставьте названия схемы и таблицы, для которой надо вывести зависимости.
11 авг 10, 18:07    [9248936]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
german_c
Member

Откуда: Таганрог
Сообщений: 4
select m.*, o.name from sys.sql_modules m,
sys.objects o
where m.object_id = o.object_id
and CHARINDEX('report46_line_id', m.definition)>0

вместо report46_line_id напиши текст, который хочешь найти - и получишь список модулей, в которых этот текст встречается
11 авг 10, 18:22    [9249033]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
Salik
Member

Откуда:
Сообщений: 297
pkarklin,

Спасибо
воспользуюсь этими динамическими функциями
и напишу скрипт который в цикле подставляет имена
процедур, функций и вьюшек.
11 авг 10, 18:50    [9249245]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
Salik
Member

Откуда:
Сообщений: 297
Senya_L,

OK
11 авг 10, 18:51    [9249253]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Как получить список всех процедур,функций,вюшек которые надо теперь отредактировать?  [new]
BJValentine
Member

Откуда:
Сообщений: 127
german_c, супер, спасибо. Даже лучше чем просто депенденсы смотреть. Обычные депенденсы не отлавливают ситуацию когда объект используется в Dynamic SQL, а этот скрипт нормально всё показал.
7 июл 16, 15:44    [19381649]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить