Lieven Cardoen

I’ve seen that working with stored procedures in MSSQL2005 sometimes raises a lot of questions in me in how to maintain all those stored procedures. I was wondering if there was a way of checking Data Object Dependencies, such as stored procedures that depend on a table.

I’ve gotten some good answers on StackOverflow.

Apparently there is a database engine stored procedure and two dynamic management functions you can use to do this like:

1. sp_depends: Displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure. References to objects outside the current database are not reported. –> will be removed in future versions of mssqlserver
2. sys.dm_sql_referencing_entities: Returns one row for each entity in the current database that references another user-defined entity by name. A dependency between two entities is created when one entity, called the referenced entity, appears by name in a persisted SQL expression of another entity, called the referencing entity. For example, if a user-defined type (UDT) is specified as the referenced entity, this function returns each user-defined entity that reference that type by name in its definition. The function does not return entities in other databases that may reference the specified entity. This function must be executed in the context of the master database to return a server-level DDL trigger as a referencing entity.
3. sys.dm_sql_referenced_entities: Returns one row for each user-defined entity referenced by name in the definition of the specified referencing entity. A dependency between two entities is created when one user-defined entity, called the referenced entity, appears by name in a persisted SQL expression of another user-defined entity, called the referencing entity. For example, if a stored procedure is the specified referencing entity, this function returns all user-defined entities that are referenced in the stored procedure such as tables, views, user-defined types (UDTs), or other stored procedures.

Also very usefull is SysComments :

SELECT DISTINCT Object_Name(ID)
FROM SysComments
WHERE text LIKE '%Table%'
AND text LIKE '%Column%'

Apparently this SysComments table is a sql server 200 system table included for backward compatibility. Instead you should be using sys.sql_modules, which is a view object.

Writing tests however seems to me the most reliable way of ensuring that nothing was broken when changing an object in the database.

Lieven Cardoen aka Johlero