如何检查sql server中的所有存储过程是否正常? [英] How to Check all stored procedure is ok in sql server?

问题描述

如果我删除表或字段，如何在 sql server 中检查所有存储过程是否正常?

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

推荐答案

我发现 Cade 的答案在制定我自己的脚本以检查数据库中的对象时很有用，所以我想我也应该分享我的脚本:

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;

这篇关于如何检查sql server中的所有存储过程是否正常?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！