List all objects and columns in SQL Server

Question

I need to list down all object inside an object and related columns they are returning from source table.

For e.g.

Procedure usp_UspTask is calling 3 functions

1. fnt_Task1
2. fnt_Task2
3. fnt_Task3

These three functions are adjoining and returning select list to usp_UspTask.

Now I want that list of tables and columns which are using inside all above three functions.

Is there any query which can return this information? I know about information schema but need related information form there.

Answer 1

For SQL Server 2008 you can use sp_depends which returns all objects that this object depends upon and all objects which depend upon this object:

EXEC SP_DEPENDS fnt_Task1

This returns two datasets:

1. Dataset which contains objects depended upon: object name, type (table/view), updated, selected, column
2. Dataset which contains objects depending upon this object: name and type

Note sp_depends is deprecated and for 2012 and later you should use:

• sys.dm_sql_referencing_entities and
• sys.dm_sql_referenced_entities