2

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!

2

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

  • Hi. This is awesome! it worked for 2 functions, but strangely the 3rd functions object id wasn't even in the table. So I did a bit of googling and came across sys.sql_expression_dependencies which had all the objects. Details in my answer below. Also thank you, I am marking this as answered. – Kent Pawar May 15 '15 at 4:51
1

* Please Read *

As noted in @Spörri answer above, sys.sql_dependencies is deprecated.


sys.sql_dependencies (Transact-SQL) :

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.sql_expression_dependencies instead.


I observed among 3 functions, one functionas object id was missing in the deprecated table sys.sql_dependencies, and I found all in the sys.sql_expression_dependencies table instead. So use the below to get the dependencies:

select * 
from sys.sql_expression_dependencies
where referencing_id =  object_id('schema.object_name') 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.