CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlinformation-schema
Rahul Neekhra
Rahul Neekhra

Reputation: 810

List all objects and columns in SQL Server

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.

Upvotes: 1

Views: 1290

Answers (1)

Steve Ford
Steve Ford

Reputation: 7753

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

Upvotes: 2

Related Questions