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?

Answer 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; 

Answer 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             )