How hard is it to determine what procedures/functions no longer compile?

Question

In sql server how difficult would it be to determine what procedures/functions no longer compile? In other words, if I scripted out alter statements for all procedures and functions in a database, I'd like to know which of these statements are going to fail?

I've been working on cleaning up a database I've inherited which has gone through years of changes and I'd like to what objects are going to raise errors when something tries to execute it.

Answer 1

I think the easiest way to do this, assuming that you have lots of these procedures and functions, would be to script them all, like you suggested, and then execute them all.

From the execution, you can see which failed.

Answer 2

Extract the database into a Visual Studio Database Project. Then start hacking away objects. The VSDB can validate the project again and detect broken dependencies. When you're happy with the results, use the VSDB diff capabilities to see all the objects you've dropped, or use VSDB deployment capabilities to actually copy out your new schema into the database.

Answer 3

Try this

Call sp_refreshsqlmodule on all non-schema bound stored procedures:

DECLARE @template AS varchar(max) 
SET @template = 'PRINT ''{OBJECT_NAME}'' 
EXEC sp_refreshsqlmodule ''{OBJECT_NAME}'' 

' 

DECLARE @sql AS varchar(max) 

SELECT  @sql = ISNULL(@sql, '') + REPLACE(@template, '{OBJECT_NAME}', 
                                          QUOTENAME(ROUTINE_SCHEMA) + '.' 
                                          + QUOTENAME(ROUTINE_NAME)) 
FROM    INFORMATION_SCHEMA.ROUTINES 
WHERE   OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' 
                                 + QUOTENAME(ROUTINE_NAME)), 
                       N'IsSchemaBound') IS NULL 
        OR OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' 
                                    + QUOTENAME(ROUTINE_NAME)), 
                          N'IsSchemaBound') = 0 

        EXEC ( 
              @sql 
            ) 

This works for all views, functions and SPs. Schemabound objects won't have problems and this can't be run on them, that's why they are excluded.

Note that it is still possible for SPs to fail at runtime due to missing tables - this is equivalent to attempting to ALTER the procedure.

Note also that just like ALTER, it will lose extended properties on UDFs - I script these off and restore them afterwards.