主页 / dba / 问题 / 18824
Accepted
Petter Brodin
Asked: 2012-06-06 02:34:22 +0800 CST

在 SQL Server 2008 中调用系统存储过程时使用正确的数据库

  • 772

我认为在运行自定义系统存储过程时使用哪个数据库时我缺少一些东西。我的存储过程中有以下内容（为简洁起见进行了编辑）：

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

问题是当我从 mydb 调用过程时，主表用于初始检查，而不是当前数据库。如果我编写 mydb.INFORMATION_SCHEMA.TABLES 它可以工作，但这并不是真正的解决方案，因为它破坏了将其维护为单个 SP 而不是每个 DB 一个 SP 的全部意义。

有任何想法吗？我尝试将 DB 名称作为参数传递并使用“use @db_name”启动 SP，但显然存储过程不允许使用语句。

提前感谢所有帮助。

sql-server stored-procedures

2 个回答

  1. Best Answer

    您需要将存储过程标记为系统对象以获得您想要的行为（下面的完整示例测试为在 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*/

    这是一种未记录的方法，可能在未来的版本中不起作用。

    • 5

  2. 替代方案：使用动态 SQL。不，在受控的管理功能中，动态 SQL 并不是它通常被认为是邪恶的东西。

    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

    现在您可以从任何其他数据库调用：

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

    或者简单地说：

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

    您甚至可以在模型（和所有现有数据库）中创建同义词，如下所示：

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

    （现在您可以取消上述调用的前缀。）

    虽然您的通话要复杂一步，但这种方法的好处是：

    • 你可以把这个过程放在你想要的任何实用程序数据库中，而不是用你的过程搞砸master。例如，如果您迁移到新服务器，对 master 中的非系统对象的依赖可能会成为一个问题。
    • 您不依赖未记录、不受支持和不保证向前兼容的功能。
    • 您可以从任何数据库中为任何数据库调用它，而无需依赖当前的数据库上下文。
    • 2

相关问题