Back to OIM Explorer

dbo.QBM_FGICountTablesUsedByCode

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 3.818 characters

Interpretation

Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FSQRemoveComment source text reference
references source dbo.QBM_FSQRemoveComment_i source text reference

References

Referenced By

dbo.QBM_PGICountTablesUsedByCode

Complete Source

1CREATE FUNCTION dbo.QBM_FGICountTablesUsedByCode(2  @startobj varchar(30)3) RETURNS int4AS5BEGIN6  DECLARE @erg int7  DECLARE @elements TABLE(Referencing varchar(30) collate database_default,8  Referenced varchar(30) collate database_default,9  ReferencedType varchar(30) collate database_default,10  ReferenceCount int DEFAULT 0,11  Referencing_id int,12  Niveau int)13  DECLARE @lauf int14  DECLARE @Niveau int = 115  INSERT INTO @elements(Referencing,16  Referenced,17  ReferencedType,18  ReferenceCount,19  Referencing_id,20  Niveau)21  SELECT22  LEFT(o.name,23  30),24  LEFT(o.name,25  30),26  o.type,27  1,28  o.object_id,29  @Niveau30  FROM sys.objects o31    WITH(readpast)32  WHERE33    o.name = @startobj34  SELECT @lauf = @@ROWCOUNT35  WHILE @lauf > 036  BEGIN37    SELECT @Niveau += 138    INSERT INTO @elements(Referencing,39    Referenced,40    ReferencedType,41    Referencing_id,42    Niveau)43    SELECT44      x.Referencing,45      x.referenced,46      x.referencedType,47      x.Referencing_id,48      @Niveau49    FROM(50    SELECT51      DISTINCT vo.Referenced AS Referencing,52      LEFT(c.name, 30) AS referenced,53      c.type AS ReferencedType,54      s.object_id AS Referencing_id,55      vo.ReferenceCount AS OldCount56    FROM @elements vo57    JOIN sys.objects s58      WITH(readpast)59      ON vo.Referenced = s.name collate database_default AND vo.ReferencedType = s.type collate database_default60    JOIN sys.sql_expression_dependencies dep61      WITH(readpast)62      ON dep.referencing_id = s.object_id63    JOIN sys.objects c64      WITH(readpast)65      ON c.object_id = dep.referenced_id66    WHERE67      NOT EXISTS(68    SELECT TOP 1 169    FROM @elements e70    WHERE71      e.Referencing = vo.Referenced AND e.Referenced =72    LEFT(c.name, 30) collate database_default AND e.ReferencedType = c.type collate database_default)) AS x73    SELECT @lauf = @@rowcount74  END75  DECLARE @Referencing varchar(30)76  DECLARE @referenced varchar(30)77  DECLARE @referencingOld varchar(30) = '#'78  DECLARE @Referencing_ID int79  DECLARE @Code nvarchar(max)80  DECLARE @CodeShorter nvarchar(max)81  DECLARE @CountReference int82  DECLARE schrittweise CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR83  SELECT84    e.Referencing,85    e.Referenced,86    e.Referencing_id87  FROM @elements e88  ORDER BY e.Niveau ASC,89  e.Referencing ASC,90  len(e.Referenced) DESC91  OPEN schrittweise92  FETCH NEXT93  FROM schrittweise INTO @referencing,94  @referenced,95  @referencing_id96  WHILE(@@fetch_status <> -1)97  BEGIN98    IF @Referencing <> @referencingOld99    BEGIN100      SELECT101        TOP 1 @Code = dbo.QBM_FSQRemoveComment_i(m.definition,102        0)103      FROM sys.sql_modules m104      WHERE105        m.object_id = @Referencing_ID106      SELECT @referencingOld = @Referencing107    END108    SELECT109      @CodeShorter = replace(@code,110      N ' ' + @referenced + N ' ',111      N '')112    SELECT113      @CountReference =(DATALENGTH(@code) - DATALENGTH(@CodeShorter)) / DATALENGTH(N ' ' + @referenced + N ' ')114    SELECT @code = @CodeShorter115    SELECT116      @CodeShorter = replace(@code,117      N ' ' + @referenced + nchar(13),118      N '')119    SELECT120      @CountReference +=(DATALENGTH(@code) - DATALENGTH(@CodeShorter)) / DATALENGTH(N ' ' + @referenced + nchar(13))121    SELECT @code = @CodeShorter122    SELECT123      @CodeShorter = replace(@Code,124      N 'dbo.' + @referenced,125      N '')126    SELECT127      @CountReference +=(DATALENGTH(@code) - DATALENGTH(@CodeShorter)) / DATALENGTH(N 'dbo.' + @referenced)128    SELECT @code = @CodeShorter129    UPDATE @elements130    SET ReferenceCount = @CountReference131    FROM @elements e132    WHERE133      e.Referencing = @Referencing AND e.Referenced = @referenced134    FETCH NEXT135    FROM schrittweise INTO @referencing,136    @referenced,137    @referencing_id138  END139  CLOSE schrittweise deallocate schrittweise140  SELECT @lauf = 2141  WHILE @lauf < @niveau142  BEGIN143    UPDATE @elements144    SET ReferenceCount = e.ReferenceCount * vor.ReferenceCount145    FROM @elements e146    JOIN(147    SELECT148      v.Referenced,149      sum(v.ReferenceCount) AS ReferenceCount150    FROM @elements v151    WHERE152      v.Niveau = @lauf -1153    GROUP BY v.Referenced) AS vor154      ON e.Referencing = vor.Referenced155    WHERE156      e.Niveau = @lauf157    SELECT @lauf += 1158  END159  SELECT @erg = sum(e.ReferenceCount)160  FROM @elements e161  WHERE162    e.ReferencedType = 'U'163  RETURN(@erg)164END

Open raw exported source

1  create   function dbo.QBM_FGICountTablesUsedByCode(@startobj varchar(30)) returns int as begin declare @erg int declare @elements table (Referencing2 varchar(30) collate database_default , Referenced varchar(30) collate database_default , ReferencedType varchar(30) collate database_default , ReferenceCount3 int default 0 , Referencing_id int  , Niveau int ) declare @lauf int declare @Niveau int = 1 insert into @elements (Referencing, Referenced, ReferencedType4, 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, ReferencedType6  , Referencing_id, Niveau) select x.Referencing, x.referenced, x.referencedType  , x.Referencing_id, @Niveau from ( select distinct vo.Referenced as Referencing7, 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.objects8 s with (readpast) on vo.Referenced = s.name collate database_default and vo.ReferencedType = s.type collate database_default join sys.sql_expression_dependencies9 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 (select10 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.type11 collate database_default ) ) as x  select @lauf = @@rowcount    end    declare @Referencing varchar(30) declare @referenced varchar(30) declare @referencingOld12 varchar(30) = '#' declare @Referencing_ID int declare @Code nvarchar(max) declare @CodeShorter nvarchar(max) declare @CountReference int declare schrittweise13 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.Referencing14 asc, len(e.Referenced) desc  OPEN schrittweise FETCH NEXT FROM schrittweise into @referencing, @referenced, @referencing_id WHILE (@@fetch_status <> -115) BEGIN if @Referencing <> @referencingOld begin select top 1 @Code = dbo.QBM_FSQRemoveComment_i(m.definition, 0 ) from sys.sql_modules m where m.object_id16 = @Referencing_ID select @referencingOld = @Referencing end  select @CodeShorter = replace(@code, N' '+ @referenced + N' ', N'') select @CountReference17 = (DATALENGTH(@code) - DATALENGTH(@CodeShorter) ) / DATALENGTH(N' '+ @referenced + N' ') select @code = @CodeShorter select @CodeShorter = replace(@code18, N' '+ @referenced + nchar(13), N'') select @CountReference += (DATALENGTH(@code) - DATALENGTH(@CodeShorter) ) / DATALENGTH(N' '+ @referenced + nchar(1319)) select @code = @CodeShorter select @CodeShorter = replace(@Code, N'dbo.'+ @referenced, N'') select @CountReference += (DATALENGTH(@code) - DATALENGTH20(@CodeShorter) ) / DATALENGTH( N'dbo.'+ @referenced) select @code = @CodeShorter update @elements set ReferenceCount = @CountReference from @elements e21 where e.Referencing = @Referencing and e.Referenced = @referenced FETCH NEXT FROM schrittweise into @referencing, @referenced, @referencing_id END close22 schrittweise deallocate schrittweise       select @lauf = 2 while @lauf < @niveau  begin update @elements set ReferenceCount = e.ReferenceCount * vor.ReferenceCount23 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