Sunday, October 5, 2008

Impact Analysis for Database Changes

When you update a stored proc you may wonder if the stored proc is being called by another stored proc, or if you are updating a view you may wonder if your view is referenced by another view. In SQL Server Management Studio, they have greatly improved the ability to do impact analysis. Simply right click on the view/table/stored proc and click "View Dependencies." You can determine which views depend on this object and what objects this object depends on.

Second Check
Another option is to run a quick query to see if a field name or view/table name is referenced within a view definition or stored proc definition within the syscomments table. This isn't as reliable as the built-in functionality, but it serves as a second check.

select distinct name from syscomments a
inner join dbo.sysobjects b on =
where text like '%orderdescription%'

No comments: