如何执行另一个数据库的存储过程? [英] How to execute store procedure for another DB?

问题描述

我有一个存储过程，应该可以在我的MS Sql Server上任何数据库的任何表上执行. EXEC和USE语句的大多数组合都不会产生任何结果.这是存储过程:

I have a stored procedure that should be able to be executed on any table of any database on my MS Sql Server. Most of the combination of EXEC and USE statements didn't result in anything. Here is the stored procedure:

CREATE PROCEDURE [dbo].[usp_TrimAndLowerCaseVarcharFields]
(
    @Database VARCHAR(200),
    @TableSchema VARCHAR(200),
    @TableName VARCHAR(200)
)
AS
BEGIN
    DECLARE @sSql VARCHAR(MAX)

    SET @Database = '[' + @Database + ']'
    SET @sSql = ''

    -- Create first part of a statement to update all columns that have type varchar
    SELECT @sSql = @sSql + COLUMN_NAME + ' = LOWER(RTRIM(' + COLUMN_NAME + ')), ' 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE DATA_TYPE = 'varchar'
    AND TABLE_CATALOG = @Database
    AND TABLE_SCHEMA = @TableSchema
    AND TABLE_NAME = @TableName

    SET @sSql = 'UPDATE ' + @Database + '.' + @TableSchema + '.' + @TableName + ' SET ' + @sSql

    -- Delete last two symbols (', ')
    SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)

    EXEC(@sSql)
END

请告诉我要在[OtherDB].[TargetTable]上执行它的操作.

Please, advice what I have to do to execute it on [OtherDB].[TargetTable].

推荐答案

您可以完全限定表和存储过程.换句话说，您可以执行以下操作:

You can fully qualify both tables and stored procedures. In other words you can do this:

UPDATE [OtherDB].[Schema].[targetTable] SET ...

看来您已经在proc中执行了此操作.

It appears you are doing this in your proc already.

您还可以使用完全合格的名称来执行存储过程-例如

You can also EXEC a stored procedure using the Fully Qualified name - e.g.

EXEC [OtherDB].[dbo].[usp_TrimAndLowerCaseVarcharFields]

老实说，您的proc看起来不错，您是否收到任何错误消息?如果是这样，请张贴它们.另外，请确保您的用户有权访问另一个数据库.

Honestly, your proc looks fine, are you receiving any error messages? If so please post them. Also, make sure your user has access to the other DB.

这篇关于如何执行另一个数据库的存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！