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.

Sql Server understands dependencies between registered objects eg stored tables, stored procedures, triggers. a first pass would be to determine which dependencies are no longer satisfied, and cull those. that aside, determining what functions "do not work" is tantamount to solving the Halting Problem - and we are left with brute force "try it and see". — johnny g, Jun 09 '10 at 15:34

score 1 · Answer 1 · answered Jun 09 '10 at 15:33

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.

score 1 · Accepted Answer · edited May 23 '17 at 10:24

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.

this did not work as expected, errors were thrown when attempting to execute this — David, Jun 14 '10 at 19:17
@Dave And the errors it throws are the objects which don't work, right? — Cade Roux, Jun 14 '10 at 19:40
@Cade Roux This doesn't work exactly for objects which have been renamed, do you have a remedy for that? — David, Jun 15 '10 at 15:18
@Dave - Unfortunately, I have to manually fix those through the UI. — Cade Roux, Jun 15 '10 at 16:25

score 0 · Answer 3 · answered Jun 09 '10 at 15:30

0

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.

answered Jun 09 '10 at 15:30

rlb.usa

14,942
16
80
128

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

3 Answers3