SQL 如何检查sql server中的所有存储过程是否正常？

Question

提问by leo

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

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

Answer 1

回答by Michael Petito

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:

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

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

回答by Cade Roux

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

它不会捕获所有内容（动态 SQL 或后期绑定对象），但它可能很有用 - 在所有非模式绑定存储过程上调用 sp_refreshsqlmodule（您可以先调用它以确保更新依赖项，然后查询依赖项，或调用之后，看看是否有任何损坏）：

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
            )

Answer 3

回答by Daniel Hillebrand

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

除了 Michael Petito 的脚本之外，您还可以检查 SP 中后期绑定对象的问题（延迟名称解析），如下所示：

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

Answer 4

回答by Danny G

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

我基本上做了同样的事情，但将它写成 CURSORless，这是非常快的。

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

Answer 5

回答by John Hartsock

Couple of ways that come to mind

想到的几种方法

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

最明显的方式运行程序
在删除表或字段之前检查表的依赖关系。然后检查那些依赖程序
在所有程序上生成脚本并搜索该字段或表
查询系统对象

Answer 6

回答by John Hartsock

None of the answers given can find the error resulting from renaming or dropping a table
but be happy, I have a solution on SQL Server 2017 and higher versions:

给出的答案都不能找到因重命名或删除表
而导致的错误，但很高兴，我在 SQL Server 2017 及更高版本上有一个解决方案：

DECLARE @NumberRecords INT
DECLARE @RowCount INT
DECLARE @Name NVARCHAR(MAX)
DECLARE @Command NVARCHAR(MAX)
DECLARE @Result int
DECLARE @Names TABLE (
    [RowId] INT NOT NULL    IDENTITY(1, 1),
    [Name]  NVARCHAR(MAX),
    [Type]  NVARCHAR(MAX)
)

INSERT INTO @Names
SELECT
        QUOTENAME(SCHEMA_NAME([Objects].schema_id)) + '.' + QUOTENAME(OBJECT_NAME([Objects].object_id)) [Name],
        type_desc [Type]
FROM sys.objects [Objects]
WHERE type_desc IN ('SQL_STORED_PROCEDURE',
                    'SQL_TRIGGER',
                    'SQL_SCALAR_FUNCTION',
                    'SQL_TABLE_VALUED_FUNCTION',
                    'SQL_INLINE_TABLE_VALUED_FUNCTION',
                    'VIEW')
ORDER BY [Name]

SET @RowCount = 1
SET @NumberRecords = (SELECT COUNT(*) FROM @Names)
WHILE (@RowCount <= @NumberRecords)
BEGIN

    SELECT @Name = [Name]
    FROM @Names
    WHERE [RowId] = @RowCount

    SET @Command = N'EXEC sp_refreshsqlmodule ''' + @Name + ''''

    BEGIN TRY

        EXEC @Result = sp_executesql @Command

        IF @Result <> 0
        BEGIN

            RAISERROR('Failed', 16, 1)

        END
        ELSE
        BEGIN

            IF (NOT EXISTS (SELECT *
                            FROM sys.dm_sql_referenced_entities(@Name, 'OBJECT')
                            WHERE [is_incomplete] = 1))
            BEGIN

                DELETE
                FROM @Names
                WHERE [RowId] = @RowCount

            END

        END

    END TRY
    BEGIN CATCH

        -- Nothing

    END CATCH

    SET @RowCount = @RowCount + 1

END

SELECT  [Name],
        [Type]
FROM @Names

Answer 7

回答by ???? ???? ?????

I tried "Cade Roux" Answer , it went wrong and I fixed it as following

我尝试了“Cade Roux”答案，它出错了，我按如下方式修复了它

 SELECT 'BEGIN TRAN T1;' UNION
    SELECT   REPLACE('BEGIN TRY
    EXEC sp_refreshsqlmodule ''{OBJECT_NAME}''
      END TRY
      BEGIN CATCH
    PRINT ''{OBJECT_NAME} IS INVALID.'' 
     END CATCH', '{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
                              UNION 
                            SELECT  'ROLLBACK TRAN T1;'

Answer 8

回答by Vasiliy Fofanov

Same idea, but more universal - you check all user defined objects with bodies And it shows you error during compiling. This is really useful after renaming/removing objects/columns etc

相同的想法，但更通用 - 您检查所有用户定义的对象和主体，并在编译期间显示错误。这在重命名/删除对象/列等后非常有用

Just run it after database schema update to make sure that all body objects still valid

只需在数据库架构更新后运行它以确保所有主体对象仍然有效

DECLARE @obj_name AS sysname, @obj_type AS sysname

DECLARE obj_cursor CURSOR FOR 
    SELECT SCHEMA_NAME(o.schema_id) + '.' + o.name, o.type_desc 
    FROM sys.objects o 
    INNER JOIN sys.sql_modules m ON o.object_id = m.object_id
    WHERE o.is_ms_shipped = 0 AND m.is_schema_bound = 0 
    ORDER BY o.type_desc, SCHEMA_NAME(o.schema_id), o.name

OPEN obj_cursor 
FETCH NEXT FROM obj_cursor INTO @obj_name, @obj_type

WHILE (@@FETCH_STATUS <> -1) 
BEGIN
    BEGIN TRY
        EXEC sp_refreshsqlmodule @obj_name
        --PRINT 'Refreshing ''' + @obj_name + ''' completed'
    END TRY
    BEGIN CATCH
        PRINT 'ERROR - ' + @obj_type + ' ''' + @obj_name + ''':' + ERROR_MESSAGE()
    END CATCH
    FETCH NEXT FROM obj_cursor INTO @obj_name, @obj_type
END 

CLOSE obj_cursor
DEALLOCATE obj_cursor

Answer 9

回答by Divya Srivastav

Once I made change to a table such as column rename, I have to alter all the stored procedures, functions and views that refer the table column. Obviously I have to manually alter them one by one. But my database contains hundreds of objects like these. So I wanted to make sure I have altered all the depending objects. One solution is to recompile all the objects (via a script). But recompilation happens on each object's next execution only. But what I want is to validate them and get the details now.

一旦对表进行了更改（例如列重命名），就必须更改所有引用该表列的存储过程、函数和视图。显然我必须一一手动更改它们。但是我的数据库包含数百个这样的对象。所以我想确保我已经改变了所有依赖的对象。一种解决方案是重新编译所有对象（通过脚本）。但是重新编译仅在每个对象的下一次执行时发生。但我想要的是验证它们并立即获取详细信息。

For that I can use “sp_refreshsqlmodule” instead of “sp_recompile”. This will refresh each object and throws an error if its not parsing correctly. Here is the script below;

为此，我可以使用“ sp_refreshsqlmodule”而不是“ sp_recompile”。这将刷新每个对象并在其解析不正确时抛出错误。这是下面的脚本；

 -- table variable to store procedure names
    DECLARE @tblObjects TABLE (ObjectID INT IDENTITY(1,1), ObjectName 
    sysname)

   -- get the list of stored procedures, functions and views
    INSERT INTO @tblObjects(ObjectName)
    SELECT '[' + sc.[name] + '].[' + obj.name + ']'
    FROM sys.objects obj
    INNER JOIN sys.schemas sc ON sc.schema_id = obj.schema_id
    WHERE obj.[type] IN ('P', 'FN', 'V') -- procedures, functions, views

    -- counter variables
    DECLARE @Count INT, @Total INT
    SELECT @Count = 1
    SELECT @Total = COUNT(*) FROM @tblObjects

    DECLARE @ObjectName sysname

    -- start the loop
    WHILE @Count <= @Total BEGIN

    SELECT @ObjectName = ObjectName
    FROM @tblObjects
    WHERE ObjectID = @Count

    PRINT 'Refreshing... ' + @ObjectName

    BEGIN TRY
        -- refresh the stored procedure
        EXEC sp_refreshsqlmodule @ObjectName
    END TRY
    BEGIN CATCH
        PRINT 'Validation failed for : ' + @ObjectName + ', Error:' + 
        ERROR_MESSAGE() + CHAR(13)
    END CATCH

    SET @Count = @Count + 1

    END

If any object throws an error I can now attend to it and manually fix the issue with it.

如果任何对象抛出错误，我现在可以处理它并使用它手动修复问题。

Answer 10

回答by Dejan Dozet

My approach was a little bit different. I've created alter script for a bunch of procs in SSMS and then waited for few seconds so SSMS process them and Ive got what I wanted:

我的方法有点不同。我已经为 SSMS 中的一堆 proc 创建了更改脚本，然后等待了几秒钟，以便 SSMS 处理它们，我得到了我想要的：

O then SSMS right border a red dot for any line in error, which I can easily check, correct and later execute same script to update with correct values.

O 然后 SSMS 右边界为任何错误行添加一个红点，我可以轻松检查、更正并稍后执行相同的脚本以使用正确的值进行更新。

SQL 如何检查sql server中的所有存储过程是否正常？

提问by leo

回答by Michael Petito

回答by Cade Roux

回答by Daniel Hillebrand

回答by Danny G

回答by John Hartsock

回答by John Hartsock

回答by ???? ???? ?????

回答by Vasiliy Fofanov

回答by Divya Srivastav

回答by Dejan Dozet

相关推荐

最近更新

标签

SQL 如何检查sql server中的所有存储过程是否正常？

提问by leo

回答by Michael Petito

回答by Cade Roux

回答by Daniel Hillebrand

回答by Danny G

回答by John Hartsock

回答by John Hartsock

回答by ???? ???? ?????

回答by Vasiliy Fofanov

回答by Divya Srivastav

回答by Dejan Dozet

相关推荐

SQL 中的索引是什么？

SQL: ... WHERE X IN (SELECT Y FROM ...)

vb6 ADODB 连接字符串到 sql server 2008

错误 18456。状态 6“正在尝试将 NT 帐户名用于 SQL Server 身份验证。”

相关推荐

最近更新

标签