主页 / dba / 问题 / 343638
Accepted
SE1986
Asked: 2024-11-16 00:57:28 +0800 CST

名称解析如何与临时存储过程一起工作？

  • 772

假设我有以下两个数据库

USE [master]
CREATE DATABASE Jack
GO

CREATE DATABASE Jill
GO

USE Jack
GO
CREATE TABLE JacksTable
(
    Id INT
)
INSERT INTO JacksTable VALUES (1)

如果我在 Jack 数据库上下文中创建以下临时存储过程：

USE Jack
GO

CREATE PROC #Temp
AS
    SELECT  *
    FROM    JacksTable
GO

然后在Jack数据库中执行：

USE Jack
GO
EXEC #Temp

我得到了结果1

如果我在 Jill 数据库上下文中运行：

USE Jill
GO
EXEC #Temp

我明白了1

现在，如果我放弃这个程序

DROP PROC #Temp

并在 Jill 数据库上下文中再次创建该过程：

USE Jill
GO

CREATE PROC #Temp
AS
    SELECT  *
    FROM    JacksTable 
GO

然后运行它

USE Jill
GO    
EXEC #Temp

我收到错误

Msg 208, Level 16, State 1, Procedure #Temp, Line 4 [Batch Start Line 74]
Invalid object name 'JacksTable'.

如果我尝试从 Jack 数据库执行它：

USE Jack
GO
EXEC #Temp

我明白了

Msg 208, Level 16, State 1, Procedure #Temp, Line 4 [Batch Start Line 74]
Invalid object name 'JacksTable'.

如果我放弃这个程序

DROP PROC #Temp

再次在 Jill 数据库上下文中重新创建

USE Jill
GO

CREATE PROC #Temp
AS
    SELECT  *
    FROM    JacksTable 
GO

这次从 Jack 数据库运行

USE Jack
GO
EXEC #Temp

我得到了同样的绑定错误

Msg 208, Level 16, State 1, Procedure #Temp, Line 4 [Batch Start Line 74]
Invalid object name 'JacksTable'.

这告诉我 SQL Server 尝试将临时过程中的对象绑定到创建临时存储过程时的上下文中的数据库。

这篇文章建议

首次执行存储过程时，查询处理器从 sys.sql_modules 目录视图中读取存储过程的文本，并检查该过程使用的对象名称是否存在。

这项检查针对哪个数据库执行？我认为是 tempdb，因为临时存储过程定义保存在该数据库中？如果是这样，那么该解决方案在所有情况下都会失败（除非我们在 tempdb 中创建了表），因此不可能是那样。

根据我上面看到的内容，它似乎是在创建临时存储过程时处于范围内的数据库（而不是对象定义持久化的数据库 - tempdb）。但是，如果确实如此，SQL Server 如何知道它是哪个数据库以及它存储在哪里？我检查了sys.sql_expression_dependencies、和sys.sql_modules，但似乎没有一个有这些信息sys.objectssys.procedures

sql-server

1 个回答

  1. Best Answer

    如果你在创建 proc 后运行SELECTagainst ，你会看到它写入的唯一位置是sys.fn_dblogtempdb

    • sys.sysschobjs
    • sys.syssingleobjrefs
    • sys.sysobj值
    • sys.syssoftobjrefs

    只有通过 DAC 连接时才能看到这些，但我刚刚这样做了，看起来indepid有tempdb.sys.syssingleobjrefs这个

    SELECT p.id, 
       refs.indepid AS potential_database_id,
       p.name AS proc_name, 
       REVERSE(CAST(REVERSE(imageval) AS NVARCHAR(MAX))) AS proc_definition
FROM tempdb.sys.sysschobjs p
JOIN tempdb.sys.syssingleobjrefs refs ON p.id = refs.depid AND refs.class = 70 AND refs.depsubid = 0
JOIN tempdb.sys.sysobjvalues v on v.objid =p.id 
WHERE p.name LIKE '#Temp%' and p.type = 'P'

    如果不使用 DAC，搜索sys.all_sql_modules对象并不能显示出任何获取此信息的希望。WHERE definition LIKE '%syssingleobjrefs%' AND definition like '%indepid%'

    sys.sql_modules确实查看了该列以确定execute_as_principal_id但在这种情况下正在进行过滤 class = 22并且我观察了70上面返回的条目。

    • 4

相关问题