How to Check all stored procedure is ok in sql server?

Question

How to check all stored procedure is ok in sql server if I drop a table or fields?

Solution 1

I found Cade's answer useful in formulating my own script for checking objects in a database, so I thought I'd share my script as well:

DECLARE @Name nvarchar(1000);
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;

DECLARE ObjectCursor CURSOR FAST_FORWARD FOR
SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id))
FROM sys.objects o
WHERE type_desc IN (
'SQL_STORED_PROCEDURE',
'SQL_TRIGGER',
'SQL_SCALAR_FUNCTION',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'VIEW')
    --include the following if you have schema bound objects since they are not supported
    AND ISNULL(OBJECTPROPERTY(o.object_id, 'IsSchemaBound'), 0) = 0
;

OPEN ObjectCursor;

FETCH NEXT FROM ObjectCursor INTO @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @Name + '''';
    --PRINT @Sql;

    BEGIN TRY
        EXEC @Result = sp_executesql @Sql;
        IF @Result <> 0 RAISERROR('Failed', 16, 1);
    END TRY
    BEGIN CATCH
        PRINT 'The module ''' + @Name + ''' does not compile.';
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    END CATCH

    FETCH NEXT FROM ObjectCursor INTO @Name;
END

CLOSE ObjectCursor;
DEALLOCATE ObjectCursor;

Solution 2

It won't catch everything (dynamic SQL or latebound objects), but it can be useful - call sp_refreshsqlmodule on all non-schema bound stored procedures (you can call it before to ensure that dependencies are updated and then query the dependencies, or call it afterwards and see if anything is broken):

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
            )

Solution 3

In addition to the script from Michael Petito you can check for issues with late-bound objects in SPs (deferred name resolution) like this:

-- Based on comment from http://blogs.msdn.com/b/askjay/archive/2012/07/22/finding-missing-dependencies.aspx
-- Check also http://technet.microsoft.com/en-us/library/bb677315(v=sql.110).aspx

select o.type, o.name, ed.referenced_entity_name, ed.is_caller_dependent
from sys.sql_expression_dependencies ed
join sys.objects o on ed.referencing_id = o.object_id
where ed.referenced_id is null

Solution 4

I basically did the same thing, but wrote it to be CURSORless which is super fast.

DECLARE @Name nvarchar(1000);
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;

DECLARE @Objects TABLE (
    Id INT IDENTITY(1,1),
    Name nvarchar(1000)
)

INSERT INTO @Objects
SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id))
FROM sys.objects o
WHERE type_desc IN (
'SQL_STORED_PROCEDURE',
'SQL_TRIGGER',
'SQL_SCALAR_FUNCTION',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'VIEW')
    --include the following if you have schema bound objects since they are not supported
    AND ISNULL(OBJECTPROPERTY(o.object_id, 'IsSchemaBound'), 0) = 0

DECLARE @x INT
DECLARE @xMax INT

SELECT @xMax = MAX(Id) FROM @Objects
SET @x = 1

WHILE @x < @xMax
BEGIN
    SELECT @Name = Name FROM @Objects WHERE Id = @x

    SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @Name + '''';
    --PRINT @Sql;

    BEGIN TRY
        EXEC @Result = sp_executesql @Sql;
        IF @Result <> 0 RAISERROR('Failed', 16, 1);
    END TRY
    BEGIN CATCH
        PRINT 'The module ''' + @Name + ''' does not compile.';
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    END CATCH
    SET @x = @x + 1
END

Solution 5

Couple of ways that come to mind

1. Most obvious way run the procedures
2. check dependencies on the table before you drop the table or a field. then check out those dependent proceudres
3. generate scripts on all procedures and search for that field or table
4. Query sysobjects

View more solutions

Comments

[Martin Smith]

It seems sp_refreshsqlmodule can end up refreshing the wrong module however!

[Cade Roux]

@Martin Yes, I should have mentioned that. In my case, because it was an ALTER and there wasn't another procedure using the same name, I got an error, it didn't hurt anything.

[buckley]

The only way to refresh triggers is to alter them if not encrypted. (connect.microsoft.com/SQLServer/feedback/details/261905/…). So the above script gives false positives when it encounters a trigger

[ClearCloud8]

This worked really well for me. (although I don't have any triggers)

[George Mavritsakis]

Nice post, works perfect! One more addition, you have to check if object is schema bound as sp_refreshsqlmodule will give false negatives as schema bound objects are not supported. So add to your query the following: and isnull(objectproperty(o.object_id,'IsSchemaBound'),0)=0 ;

[Drew Freyling]

I'd also change line 6 to SELECT SCHEMA_NAME(o.schema_id) + '.[' + OBJECT_NAME(o.object_id) + ']' in case you have stored procedures with a dot in the name.

[Michael Petito]

@DrewFreyling Good idea! You can also use QUOTENAME to quote object names as such. I'll update the answer.

[Nicholas]

Using a while statement to loop through a table of data is basically the same as using a cursor with fast_forward. Both are going row by agonising row (RBAR). This is one situation where it is acceptable since it cannot be replaced by a set based operation.

[Dan Field]

This does perform a bit better than using the cursor, even if it still uses a while loop.

[John Zabroski]

The problem is it will still return CTE aliases and temp tables as null references.

[Amit G]

I tried this script on a stored procedure that references a non-existent table, and it didn't highlight it. Sure enough, if I just run sp_refreshsqlmodule 'mysp' it doesn't report this stored proc either.

[Nathan]

Useful script. Two other scenarios where it can fail: 1 - you have a procedure/function name that contains a single quote (sql injection via db object names - woohoo!), 2 - you have a scalar UDF that is used in a CHECK constraint on table column. I hope neither of these scenarios are common...

[tvanharp]

A warning on sp_refreshsqlmodule, if an object is renamed in SSMS or renamed using sp_rename the object name in sys.sql_modules is not changed. Running the sp_refreshview or refreshsqlmodule will rebuild the object using whatever sql_modules has for that name, which could be a different definition than what the object currently is!

[Chris Rice]

DANGER: This will re-activate any triggers that are disabled. I had a few triggers that were disabled due to a system migration that was in progress. They were re-enabled and it caused pretty much everything to crash.

[Dejan Dozet]

I know this is an old post, but I think very useful, only one question: why we need ROLLBACK TRANSACTION in the catch block?

[BunkerBilly]

Works perfectly, saved me lots of time

[Polyfun]

Yes this is better because it actually shows the original error.

[yodag123]

" (2) - you have a scalar UDF that is used in a CHECK constraint on table column". Exclude these functions by using sys.sql_expression_dependencies : Add to the where clause AND o.object_id NOT IN (select oo.object_id from sys.check_constraints s INNER JOIN sys.sql_expression_dependencies d ON d.referencing_id = s.object_id INNER JOIN sys.objects oo on oo.object_id = d.referenced_id AND oo.type = 'FN') @nathan

[Nick.McDermaid]

I've just found a proc that refers to a non existing database that saves OK and passes sp_refreshsqlmodule

[Nick.McDermaid]

This picks up procs that pass sp_refreshsqlmodule even though the objects within them are invalid

[Nitesh]

It doesnt identify a table used which has been dropped.