Find broken objects in SQL Server

You may be interested in checking out the following articles:

• Michael J. Swart: Find Missing SQL Dependencies
• eggheadcafe.com: Find broken stuff

You can test Michael J. Swart's solution as follows:

CREATE PROCEDURE proc_bad AS
    SELECT col FROM nonexisting_table
GO

SELECT
    OBJECT_NAME(referencing_id) AS [this sproc or VIEW...],
    referenced_entity_name AS [... depends ON this missing entity name]
FROM 
    sys.sql_expression_dependencies
WHERE 
    is_ambiguous = 0
    AND OBJECT_ID(referenced_entity_name) IS NULL
ORDER BY 
    OBJECT_NAME(referencing_id), referenced_entity_name;

Which returns:

+------------------------+------------------------------------------+
| this sproc or VIEW...  |  ... depends ON this missing entity name |
|------------------------+------------------------------------------|
| proc_bad               |  nonexisting_table                       |
+------------------------+------------------------------------------+

Red Gate Software's SQL Prompt 5 has a Find Invalid Objects feature that might be useful in this situation. The tool goes through the database finding objects that will give an error when executed, which sounds exactly what you want.

You can download a 14-day trial for free, so you can give it a try and see if it helps.

Paul Stephenson
SQL Prompt Project Manager
Red Gate Software

Your best bet is to start using a tool like Visual Studio Database Edition. It's role is to manage a database schema. One of the many things it will do is to throw an error when you attempt to build the database project and it contains broken objects. It will of course do much more than this. The tool is free to any user of Visual Studio Team Suite or Visual Studio Developer Edition.

 create table #BrokenObjects (Name nvarchar(500), Error nvarchar(max))
 select * into #objects from(
 select name from sys.views
 union select name from sys.procedures
 union select name from sys.tables
 )x
 declare @name nvarchar(500),@err nvarchar(max)
 while exists(select top 1 * from #objects)
 begin
 select top 1 @name = name from #objects
 begin try
 EXEC sys.sp_refreshsqlmodule @name
 end try
 begin catch
 select @err = ERROR_MESSAGE()
 insert into #BrokenObjects (name,error) values (@name,@err)
 end catch
 delete from #objects
 where name = @name
 end
 drop table #objects
 select * from #BrokenObjects
 where Error not like 'Could not find object % or you do not have permission.'

 drop table #BrokenObjects