Find objects used by specific function in Azure SQL database

Article for: SQL Server Oracle database IBM Db2

Query below return all User Defined Functions and objects used by them.

Query

select schema_name(obj.schema_id) as schema_name,
       obj.name as function_name,
       schema_name(dep_obj.schema_id) as referenced_object_schema,
       dep_obj.name as referenced_object_name,
       dep_obj.type_desc as object_type
FROM sys.objects obj
left join sys.sql_expression_dependencies dep
     on dep.referencing_id = obj.object_id
left join sys.objects dep_obj
     on dep_obj.object_id = dep.referenced_id
where obj.type in ('AF', 'FN', 'FS', 'FT', 'IF', 'TF')
    -- and obj.name = 'function_name'  -- put function name here
order by schema_name,
         function_name;

Columns

  • schema_name - schema name
  • function_name - function name
  • referenced_object_schema - schema name of the referenced object
  • referenced_object_name - name of the referenced object
  • object_type - type of referenced object

Rows

  • One row represents one referenced object by function or only function if function is not using any objects
  • Scope of rows: all objects that are used by function in database
  • Ordered by schema name and function name

Sample Result

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