Finding out what database dependencies there are with tables, views, and stored procedures

Products

• IT Management Suite

Issue/Introduction

How can I determine the database dependencies of tables, views, and stored procedures?

Environment

ITMS 8.x

Resolution

You can run the following SQL query line in Microsoft SQL Server Management Studio:

sp_depends %Database Object Name%

The database object name is the name of a table, view, stored procedure, or function.

Example:

sp_depends AC_AgentHealthInfo

The attached file is a SQL query that can run through a database and return all the dependencies in a table. There are four possible displays mode (just change the @DisplayChoice value) of this SQL script.

1. Choice one (default) shows the object type, database object name, and tables/views that it depends on (extends four columns out from the database object name).
2. Choice two shows the user's table name (a database object name) and the count of other of database objects that counts on that object.
3. Choice three shows the all table/view names and what is dependent on it (views, stored procedure)
4. Choice four shows the all table/view names and the count of other of database objects that counts on that table.
   
   
   

Note: You can use the SQL script to see the connection between the database object (tables, views, and stored procedures) to explore the database or troubleshoot possible connection in the database.