How to write a query for SQL Server 2008 that returns the dependencies of an object (Exactly what Management Studio shows with the "View Dependencies" menu)

Question

1. Management Studio connected to SQL Server 2008
2. Right click on an object and choose "View Dependencies"
3. Now you can navigate through de dependendies back and forth.

How do I get the same information programmatically? (an SQL query?)

Answer 1

Have a look at the metadata in the sys and INFORMATION_SCHEMA tables.

There is this answer, this answer, and this answer, which could all be useful.

Answer 2

Before you run the following query, replace <database_name> and <schema_name.function_name> with valid names

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) AS referencing_object_name
    ,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name
    ,*
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('<schema_name.function_name>')
ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);
GO

Answer 3

Here is another simpler way:

  SELECT DISTINCT
    O.ID ,
    O.Name AS TableName ,
    O.xtype
  FROM
    sysObjects O ( NOLOCK )
  INNER JOIN sysComments C ( NOLOCK ) ON O.ID = C.ID
  WHERE
    C.text LIKE '%<schema_name.function_name>%'
  ORDER BY
    XType ,
    TableName
Before you run the following query, replace <schema_name.function_name> with a valid name