Início / dba / Perguntas / 18824
Accepted
Petter Brodin
Asked: 2012-06-06 02:34:22 +0800 CST

Usando o banco de dados correto ao chamar um procedimento armazenado do sistema no SQL Server 2008

  • 772

Acho que há algo que estou perdendo quando se trata de qual banco de dados está sendo usado ao executar um procedimento armazenado de sistema personalizado. Eu tenho o seguinte em meu procedimento armazenado (editado para brevidade):

ALTER PROCEDURE sp_mysp
AS
IF (EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'mytable'))
BEGIN
    --Do stuff to the table
END
ELSE
BEGIN
    PRINT 'Table mytable does not exist'
END

O problema é que quando eu chamo o procedimento de mydb, a tabela Master é usada para a verificação inicial, ao invés do banco de dados atual. Se eu escrever mydb.INFORMATION_SCHEMA.TABLES funcionará, mas isso não é realmente uma solução, pois está derrotando todo o objetivo de manter isso como um único SP em vez de um SP para cada banco de dados.

Alguma ideia? Tentei passar o nome do banco de dados como parâmetro e iniciar o SP com 'use @db_name', mas aparentemente os procedimentos armazenados não permitem instruções de uso.

Agradecemos antecipadamente por toda a ajuda.

sql-server stored-procedures

2 respostas

  1. Best Answer

    Você precisa marcar seu procedimento armazenado como um objeto do sistema para obter o comportamento desejado (exemplo completo abaixo testado como funcionando em 2008 SP3)

    USE master;
GO

CREATE PROCEDURE dbo.sp_mysp
AS
SELECT *
FROM INFORMATION_SCHEMA.TABLES

GO

USE tempdb;
EXEC dbo.sp_mysp /*Returns tables from master*/

GO

USE master;
EXEC sys.sp_MS_marksystemobject sp_mysp

GO

USE tempdb;
EXEC dbo.sp_mysp /*Returns tables from tempdb*/

    Esta é uma abordagem não documentada e pode não funcionar em versões futuras.

    • 5

  2. Alternativa: usando SQL dinâmico. E não, na funcionalidade de administração controlada, o SQL dinâmico não é a coisa má que costuma ser.

    USE master;
GO
CREATE PROCEDURE dbo.sp_find_table
    @db_name    SYSNAME,
    @table_name SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    SELECT @sql = 'SELECT QUOTENAME(s.name)
      + ''.'' + QUOTENAME(t.name)
      FROM ' + @db_name + '.sys.tables AS t
      INNER JOIN ' + @db_name + '.sys.schemas AS s
      ON t.[schema_id] = s.[schema_id];';

    EXEC sp_executesql @sql;
END
GO

    Agora sua chamada de qualquer outro banco de dados pode ser:

    DECLARE @db SYSNAME = DB_NAME(DB_ID());
EXEC master.dbo.sp_find_table @db_name = @db, @table_name = 'floob';

    Ou simplesmente:

    EXEC master.dbo.sp_find_table 'tempdb', @table_name = 'floob';

    Você pode até criar um sinônimo no modelo (e em todos os bancos de dados existentes) da seguinte forma:

    CREATE SYNONYM dbo.sp_find_table FOR master.dbo.sp_find_table;
GO

    (Agora você pode retirar o prefixo das chamadas acima.)

    Embora sua chamada seja uma etapa mais complexa, os benefícios dessa abordagem:

    • você pode colocar este procedimento em qualquer banco de dados utilitário que desejar, em vez de estragar o mestre com seus procedimentos. A dependência de objetos que não são do sistema no mestre pode ser um problema se você migrar para um novo servidor, por exemplo.
    • você não depende de funcionalidade não documentada, sem suporte e sem garantia de compatibilidade futura.
    • você pode chamá-lo para qualquer banco de dados, de qualquer banco de dados, sem depender do contexto atual do banco de dados.
    • 2

relate perguntas