dbo.QBM_FGICountTablesUsedByCode

1  create   function dbo.QBM_FGICountTablesUsedByCode(@startobj varchar(30)) returns int as begin declare @erg int declare @elements table (Referencing
2 varchar(30) collate database_default , Referenced varchar(30) collate database_default , ReferencedType varchar(30) collate database_default , ReferenceCount
3 int default 0 , Referencing_id int  , Niveau int ) declare @lauf int declare @Niveau int = 1 insert into @elements (Referencing, Referenced, ReferencedType
4, ReferenceCount, Referencing_id, Niveau) select left(o.name, 30), left(o.name, 30), o.type, 1, o.object_id, @Niveau from sys.objects o with (readpast)
5 where o.name = @startobj select @lauf = @@ROWCOUNT while @lauf > 0 begin select @Niveau += 1 insert into @elements (Referencing, Referenced, ReferencedType
6  , Referencing_id, Niveau) select x.Referencing, x.referenced, x.referencedType  , x.Referencing_id, @Niveau from ( select distinct vo.Referenced as Referencing
7, left(c.name, 30) as referenced, c.type as ReferencedType , s.object_id as Referencing_id, vo.ReferenceCount as OldCount      from @elements vo join sys.objects
8 s with (readpast) on vo.Referenced = s.name collate database_default and vo.ReferencedType = s.type collate database_default join sys.sql_expression_dependencies
9 dep with (readpast) on dep.referencing_id = s.object_id join sys.objects c with (readpast) on c.object_id = dep.referenced_id where not exists (select
10 top 1 1 from @elements e where e.Referencing = vo.Referenced and e.Referenced = left(c.name, 30) collate database_default  and e.ReferencedType = c.type
11 collate database_default ) ) as x  select @lauf = @@rowcount    end    declare @Referencing varchar(30) declare @referenced varchar(30) declare @referencingOld
12 varchar(30) = '#' declare @Referencing_ID int declare @Code nvarchar(max) declare @CodeShorter nvarchar(max) declare @CountReference int declare schrittweise
13 CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR select e.Referencing, e.Referenced, e.Referencing_id  from @elements e order by e.Niveau asc, e.Referencing
14 asc, len(e.Referenced) desc  OPEN schrittweise FETCH NEXT FROM schrittweise into @referencing, @referenced, @referencing_id WHILE (@@fetch_status <> -1
15) BEGIN if @Referencing <> @referencingOld begin select top 1 @Code = dbo.QBM_FSQRemoveComment_i(m.definition, 0 ) from sys.sql_modules m where m.object_id
16 = @Referencing_ID select @referencingOld = @Referencing end  select @CodeShorter = replace(@code, N' '+ @referenced + N' ', N'') select @CountReference
17 = (DATALENGTH(@code) - DATALENGTH(@CodeShorter) ) / DATALENGTH(N' '+ @referenced + N' ') select @code = @CodeShorter select @CodeShorter = replace(@code
18, N' '+ @referenced + nchar(13), N'') select @CountReference += (DATALENGTH(@code) - DATALENGTH(@CodeShorter) ) / DATALENGTH(N' '+ @referenced + nchar(13
19)) select @code = @CodeShorter select @CodeShorter = replace(@Code, N'dbo.'+ @referenced, N'') select @CountReference += (DATALENGTH(@code) - DATALENGTH
20(@CodeShorter) ) / DATALENGTH( N'dbo.'+ @referenced) select @code = @CodeShorter update @elements set ReferenceCount = @CountReference from @elements e
21 where e.Referencing = @Referencing and e.Referenced = @referenced FETCH NEXT FROM schrittweise into @referencing, @referenced, @referencing_id END close
22 schrittweise deallocate schrittweise       select @lauf = 2 while @lauf < @niveau  begin update @elements set ReferenceCount = e.ReferenceCount * vor.ReferenceCount
23 from @elements e join (select v.Referenced, sum(v.ReferenceCount) as ReferenceCount from @elements v where v.Niveau = @lauf -1 group by v.Referenced )
24 as vor on e.Referencing = vor.Referenced where e.Niveau = @lauf select @lauf += 1 end      select @erg = sum(e.ReferenceCount) from @elements e where 
25e.ReferencedType  = 'U' return (@erg) end 
26