SQL Server : Recompiling /Refreshing Dependent Objects

Some times we add or drop column to/from a table or view. When the change hits the production we suddenly find out that a lot of Views or Procedures just became invalid and would return wrong result. As for example, the new field that was added is not available in the view that uses select * .

Here is a small stored procedure that you can call after each Alter Table or Alter View statement to make sure that all dependent Views are refreshed and all stored procedures are recompiled.  Basically it finds all dependent objects using build-in stored procedure sp_depends @objectName that takes the object name as input and returns a table of dependent objects that contains two columns 1) Object Name and 2) Object Type. We just go through the result and execute one of the built in stored procs 1) If the object type is view then we call sp_RefreshView @depObjName  2) Otherwise we call sp_Recompile @depObjName.

 

Alter Procedure sp_RefreshDependentObjects
(
@ObjectName as Varchar(100)
)
As

CREATE TABLE #DepObjs(
ObjectName Varchar(100),
ObjectType Varchar(30) )

Declare @objName Varchar(100), @objType Varchar(30)

Insert Into #DepObjs
EXEC sp_depends @ObjectName

declare objCursor CURSOR FOR
Select * From #DepObjs

Open objCursor

Fetch Next From objCursor INTO   @objName, @objType
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @objType=’view’
        Exec sp_RefreshView @objName
    ELSE
        Exec sp_recompile @objName
    FETCH NEXT FROM objCursor INTO @objName, @objType
END

Drop Table #DepObjs

Advertisement