SQL 如何为另一个数据库执行存储过程？

How to execute store procedure for another DB?

提问by Oleg Sakharov

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:

我有一个存储过程，它应该能够在我的 MS Sql Server 上的任何数据库的任何表上执行。EXEC 和 USE 语句的大多数组合都没有产生任何结果。下面是存储过程：

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

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

请建议我必须做什么才能在 [OtherDB].[TargetTable] 上执行它。

回答by Code Magician

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

您可以完全限定表和存储过程。换句话说，你可以这样做：

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

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]

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.

老实说，您的 proc 看起来不错，您是否收到任何错误消息？如果是这样，请发布它们。此外，请确保您的用户有权访问其他数据库。

回答by Elias Hossain

The table name in the query you used is wrong, it is looking up into same database, but you do need to look up from different database. So the query will be as below:

您使用的查询中的表名是错误的，它正在查找同一个数据库，但您确实需要从不同的数据库中查找。所以查询将如下所示：

SELECT @sSql = @sSql + COLUMN_NAME + ' = LOWER(RTRIM(' + COLUMN_NAME + ')), ' 
FROM [TargetDB].INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE = 'varchar'
AND TABLE_CATALOG = @Database
AND TABLE_SCHEMA = @TableSchema
AND TABLE_NAME = @TableName

-- [TargetDB] = @Database

The TargetDB will be same as your passing database (@Database)

TargetDB 将与您传递的数据库（@Database）相同

If you want to use [TargetDB] dynamically then you need to generate sql(@sSql) and the execute the sql string.

如果要动态使用 [TargetDB]，则需要生成 sql(@sSql) 并执行 sql 字符串。

回答by Ricardo Figueiredo

In this case I sugest to use the 2 sp's in SQL Server:

在这种情况下，我建议在 SQL Server 中使用 2 个 sp：

• sp_MSforeachtable

• sp_MSforeachdb

• sp_MSforeachtable

• sp_MSforeachdb

for more information, please read the article in here. hope this help.

有关更多信息，请阅读此处的文章。希望这有帮助。

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2, 
  @command3, @whereand, @precommand, @postcommand

exec @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar, 
  @command2, @command3, @precommand, @postcommand

Complete script:

完整脚本：

declare @DatabaseName varchar(max), @DatabaseCharParam nchar(1) 
declare @TableSchema varchar(max) 
declare @TableName varchar(max), @TableCharParam nchar(1) 
set @DatabaseName='DATABASENAME'; set @DatabaseCharParam='?'
set @TableSchema='dbo'
set @TableName='TABLENAME'; set @TableCharParam='$'


 -- Exemple Script to execute in each table in each database
 -- Create first part of a statement to update all columns that have type varchar
    DECLARE @sSql VARCHAR(MAX)
    set @sSql=''
    SELECT @sSql = isnull(@sSql,'') + COLUMN_NAME + ' = LOWER(RTRIM(' + COLUMN_NAME + ')),' 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE DATA_TYPE = 'varchar'
    AND TABLE_CATALOG = @DatabaseName
    AND TABLE_SCHEMA = @TableSchema
    AND TABLE_NAME = @TableName



declare @EachTablecmd1 varchar(2000)
--Prepare @EachTablecmd1 the script to execution in each table using sp_MSforeachtable (ATENTION: @Command1 are limited to varchar(2000) )
    --in sp_MSforeachtable @TableCharParam will be subtituted with owner i.e:[dbo].[TABLENAME]
set @sSql='update '+@TableCharParam+' set '+ left(@sSql,LEN(@sSql)-1) 
set @EachTablecmd1='if '''''+ @TableCharParam +'''''=''''['+@TableSchema+'].['+@TableName+']'''' '+@sSql
    --i.e.: if 'table1'='table1' update table1 set column1=LOWER(RTRIM(column1)),....

-- the @sSql for each table in a database
set @sSql ='exec sp_MSforeachtable @command1='''+@EachTablecmd1+''' ,@replacechar='''+@TableCharParam+''''



declare @EachBDcmd1 varchar(2000)
--Prepare the execution to each database using sp_MSforeachdb (ATENTION: @Command1 are limited to varchar(2000) )
set @EachBDcmd1='if '''+@DatabaseCharParam+'''='''+@DatabaseName+''' '+ @sSql
--print @EachBDcmd1
exec sp_MSforeachdb @command1=@EachBDcmd1,@replacechar=@DatabaseCharParam