How to determine which objects depend on another object in Sql Server
Introduction
Have you ever been in the situation where you needed to make a modification to an existing object in a SQL Server database but were not sure what impact the change would have on other objects? What objects depend on this object you are about to change? It can be a scary thing to make a change to a well-developed database system.
I was in that situation recently where I needed to add a parameter to an existing function. Well this function was referenced by many stored procedures and the last thing I wanted to do was to examine the SQL for each stored procedure manually. As it turns I don't need to. In SQL Server 2005 there is a system view called sys.syscomments which contains a field called "text." This field holds the code used to create an object in the database. By querying this table we are able to create a list of all objects in the database which depend on the object we're making the change to.
In order to make this query useful we'll need to join on a couple of other tables. What we want to know is the name of the schema as well as the object name. Below is the query I use whenever I’m doing a search.
SELECT
s.[name] AS ‘Schema’
,o.[name] AS ‘Object’
,o.[type] AS ‘Type’
FROM
s
INNER JOIN
o ON s.schema_id = o.schema_id
INNER JOIN
sc ON o.object_id = sc.id
WHERE
sc.text LIKE ‘%<TextToSearchFor>%’
ORDER BY
s.[name]
,o.[name];
With this query I can very quickly generate a list of all the objects that will be impacted and must be updated once the change I need to make is implemented.