Find where specific function is used in Azure SQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-01

Table of Contents:


    Article for: Azure SQL Database SQL Server Oracle database MySQL

    Query below list objects where specific function is used.

    Query

    select schema_name(o.schema_id) + '.' + o.name as [function],
           'is used by' as ref,
           schema_name(ref_o.schema_id) + '.' + ref_o.name as [object],
           ref_o.type_desc as object_type
    from sys.objects o
    join sys.sql_expression_dependencies dep
         on o.object_id = dep.referenced_id
    join sys.objects ref_o
         on dep.referencing_id = ref_o.object_id
    where o.type in ('FN', 'TF', 'IF')
          and schema_name(o.schema_id) = 'dbo'  -- put schema name here
          and o.name = 'ufnLeadingZeros'  -- put function name here
    order by [object];
    

    Columns

    • function - provided function schema name and name
    • ref - string 'is used by'
    • object - name of object with schema name which use specific function
    • object_type - type of found object

    Rows

    • One row represents one object
    • Scope of rows: all objects that are using provided function
    • Ordered by schema name, object name

    Sample Results

    List of objects that are using dbo.ufnLeadingZeros function in AdventureWorks2017 database.

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept