Início / dba / Perguntas / 343638
Accepted
SE1986
Asked: 2024-11-16 00:57:28 +0800 CST

Como a resolução de nomes funciona com procedimentos armazenados temporários?

  • 772

Digamos que eu tenha os dois bancos de dados a seguir

USE [master]
CREATE DATABASE Jack
GO

CREATE DATABASE Jill
GO

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

Se eu criar o seguinte procedimento armazenado temporário no contexto do banco de dados Jack:

USE Jack
GO

CREATE PROC #Temp
AS
    SELECT  *
    FROM    JacksTable
GO

e então execute no banco de dados Jack:

USE Jack
GO
EXEC #Temp

Eu obtive o resultado1

Se eu então executar no contexto do banco de dados Jill:

USE Jill
GO
EXEC #Temp

Eu entendo1

Agora, se eu abandonar o procedimento

DROP PROC #Temp

e crie o procedimento novamente no contexto do banco de dados Jill:

USE Jill
GO

CREATE PROC #Temp
AS
    SELECT  *
    FROM    JacksTable 
GO

e execute-o

USE Jill
GO    
EXEC #Temp

Recebo um erro

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

Se eu tentar executá-lo a partir do banco de dados Jack:

USE Jack
GO
EXEC #Temp

Eu entendo

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

Se eu então abandonar o procedimento

DROP PROC #Temp

Recrie novamente no contexto do banco de dados Jill

USE Jill
GO

CREATE PROC #Temp
AS
    SELECT  *
    FROM    JacksTable 
GO

e desta vez executado a partir do banco de dados Jack

USE Jack
GO
EXEC #Temp

Recebo o mesmo erro de ligação

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

isso me diz que o SQL Server tenta vincular os objetos no procedimento temporário ao banco de dados que estava no contexto quando o procedimento armazenado temporário foi criado.

este artigo sugere

Quando um procedimento armazenado é executado pela primeira vez, o processador de consulta lê o texto do procedimento armazenado na exibição do catálogo sys.sql_modules e verifica se os nomes dos objetos usados ​​pelo procedimento estão presentes.

Em qual banco de dados essa verificação é realizada? Eu diria que seria tempdb, pois esse é o banco de dados no qual a definição de procedimentos armazenados temporários é persistida? Se for, essa resolução falharia em todos os casos (a menos que criássemos as tabelas em tempdb), então não pode ser isso.

Com base no que vi acima, parece ser o banco de dados que estava no escopo quando o procedimento armazenado temporário foi criado (em vez do banco de dados em que a definição do objeto é persistida - tempdb). No entanto, se esse for realmente o caso, como o SQL Server sabe qual banco de dados é esse e onde ele está armazenado? Eu verifiquei sys.sql_expression_dependencies, sys.sql_modules, sys.objectse sys.procedurese nenhum parece ter essa informação

sql-server

1 respostas

  1. Best Answer

    Se você executar um SELECTcontra sys.fn_dblogdepois tempdbde criar o proc, poderá ver que os únicos lugares em que ele escreve são

    • sys.sysschobjs
    • sys.syssingleobjrefs
    • sys.sysobjvalores
    • sys.syssoftobjrefs

    Você só pode vê-los ao conectar via DAC , mas eu acabei de fazer isso e parece que indepidtem tempdb.sys.syssingleobjrefsisso

    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'

    A busca sys.all_sql_modulespor objetos WHERE definition LIKE '%syssingleobjrefs%' AND definition like '%indepid%'não mostra nada promissor para obter essas informações sem usar o DAC.

    sys.sql_modulesolha para essa coluna para determinar, execute_as_principal_idmas está filtrando class = 22nesse caso e observei 70as entradas retornadas pelo acima.

    • 4

relate perguntas