在sql server中，有什么方法可以检查架构更改是否会影响存储过程？

Question

在 SQL Server 中，有什么方法可以检查架构中的更改是否会影响存储过程（和/或视图）？
例如，更改一个表中的列名可能会破坏某些存储过程；如何检查受影响的存储过程？

Answer 1

尝试使用：

EXEC sp_depends 'YourTableName'

和/或

DECLARE @Search nvarchar(500)
SET @Search='YourTableName' --or anything else
SELECT DISTINCT
    LEFT(o.name, 100) AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY 2,1

Answer 2

使用 Visual Studio Database Edition 进行 T-SQL 开发。它会在构建过程中捕获此类问题，因为它会创建部署 .dbschema 文件。

Answer 3

在 SSMS (SQL Server Management Studio) 中，右键单击要更改的对象，然后单击“查看依赖关系”。我认为这不会从另一个数据库找到引用。

您还可以在存储过程中查找引用（如果它们未加密）。您必须在您怀疑可能引用您正在更改的对象的每个数据库中执行此操作。

选择对象.名称
,sql_modules.definition
来自 sys.sql_modules sql_modules
在 sql_modules.object_id =objects.object_id 上加入 sys.objects 对象
其中定义如“%some column name%”；

我没有发现任何东西在 100.000000% 的时间都是 100.0000% 准确的。

Answer 4

我认为做到这一点的最好方法是使用视图从实际表中抽象存储过程，并使用“WITH SCHEMABINDING”子句创建这些视图，这应该防止会破坏视图的更改...

Answer 5

商业工具，例如Red Gate 的 SQL Refactor 可以做到这一点。
我认为最近版本的Visual Studio也包含这种功能，但我没有尝试过。

据我所知，Microsoft SQL Server 本身没有内置功能可以执行此操作。 更正：我刚刚读到sp_depends 在 KM 对这篇文章的回答中...请注意，sp_depends 的用法已被弃用；它被 sys.dm_sql_referencing_entities 和 sys.dm_sql_referenced_entities 替换。

此外，如果底层存储过程使用动态 SQL，检测依赖关系的任务变得更加困难并且容易“遗漏”。

Answer 6

如果您想更改对象或列的名称，则可以使用 Red Gate Software SQL Prompt 5 将生成一个脚本，该脚本既执行重命名，又更新对其他对象中旧名称的引用。

如果您只是对依赖于列名的内容感兴趣，那么 SQL Prompt 5 还具有列依赖项功能，将鼠标悬停在脚本中的列名上会弹出一个包含对象列表的窗口指的是该列。

您可以免费下载 14 天试用版，看看这些功能是否适合您。

保罗·史蒂芬森
SQL 提示项目管理器
红门软件

Answer 7

看看这些答案：

刷新用户函数 t-SQL 上的元数据

SQL Server 关系隐藏在存储过程而不是架构中< /a>

在 SQL Server 中，如何我可以找到引用列的所有地方吗？

如何查找所有插入、更新或删除记录的存储过程？

除了动态 SQL 之外，尽可能使用 SCHEMABINDING 以及 sp_refreshsqlmodule 和 sql_dependency 对于其他所有内容都非常准确。

Answer 8

如果您使用 SQL Server

您可以在更改后使用此查询并找到存储过程或视图或...

更改后可能会出现错误

USE <Your_DataBase_Name>;

SET NOCOUNT ON;

DECLARE @name NVARCHAR(MAX) 
DECLARE @sql NVARCHAR(MAX) 
DECLARE @type CHAR(2)
DECLARE @type_desc NVARCHAR(60)
DECLARE @params NVARCHAR(MAX)

DECLARE @tblInvalid TABLE
(
  [type_desc] NVARCHAR(60) ,
  [name] NVARCHAR(MAX) ,
  [error_number] INT ,
  [error_message] NVARCHAR(MAX) ,
  [type] CHAR(2)
);       

DECLARE testSPs CURSOR FAST_FORWARD
FOR
SELECT  [name] = OBJECT_NAME(SM.[object_id]) ,
        [type] = SO.[type] ,
        SO.[type_desc] ,
        [params] = ( SELECT (
                            SELECT  CONVERT(XML, ( SELECT STUFF(( SELECT
                                                          ', ' + [name]
                                                          + '=NULL' AS [text()]
                                                          FROM
                                                          sys.parameters
                                                          WHERE
                                                          [object_id] = SM.[object_id]
                                                          FOR
                                                          XML
                                                          PATH('')
                                                          ), 1, 1, '')
                                                 ))
                     FOR    XML RAW ,
                                TYPE
                 ).value('/row[1]', 'varchar(max)')
                   )
FROM    sys.sql_modules SM
        JOIN sys.objects SO ON SO.[object_id] = SM.[object_id]
WHERE   SO.[is_ms_shipped] = 0
        AND SO.[type] = 'P'


OPEN testSPs 
FETCH NEXT FROM testSPs INTO @name, @type, @type_desc, @params

WHILE ( @@FETCH_STATUS = 0 ) 
BEGIN
    BEGIN TRY
        SET @sql = 'SET FMTONLY ON; exec ' + @name + ' ' + @params
            + '; SET FMTONLY OFF;'
        --PRINT @sql;      
        EXEC (@sql);
    END TRY
    BEGIN CATCH
        PRINT @type_desc + ', ' + @name + ', Error: '
            + CAST(ERROR_NUMBER() AS VARCHAR) + ', ' + ERROR_MESSAGE();
        INSERT  INTO @tblInvalid
                SELECT  @type_desc ,
                        @name ,
                        ERROR_NUMBER() ,
                        ERROR_MESSAGE() ,
                        @type;
    END CATCH


    FETCH NEXT FROM testSPs INTO @name, @type, @type_desc, @params
END

CLOSE testSPs  
DEALLOCATE testSPs


SELECT  [type_desc] ,
    [name] ,
    [error_number] ,
    [error_message]
FROM    @tblInvalid
ORDER BY CHARINDEX([type], ' U V PK UQ F TR FN TF P SQ ') ,
    [name];