SQL Server – List Underlying Objects Used in a Function

sql serversystem-tables

I use the below to get a list of underlying objects in a View:

SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name IN 
    (
      'View1', 'View2'
    )
ORDER BY view_name, table_name

I could not find any system table that provided the base objects in a function. Does anyone have any idea how to easily pull this information? I am dealing with many functions with multiple base DB objects and want to avoid manual effort to get this information. Thanks!

Best Answer

sp_depends will give you list of dependent objects in all current versions of SQL server but is listed as deprecated.

Here is a MSDN article with a way to find objects referencing a function and with small modifications you can get the list of object referenced by a function:

SELECT OBJECT_NAME(object_id) AS referencing_object_name
 ,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name
 ,object_name(referenced_major_id) as referenced_object
 ,*
FROM sys.sql_dependencies
--WHERE referenced_major_id = OBJECT_ID('schema.objectname')
where object_id = object_id('schema.objectname')
ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);

And as a bonus: http://beyondrelational.com/modules/2/blogs/28/posts/10399/tsql-lab-9-how-to-find-the-dependency-chain-of-a-database-object.aspx

Related Question