How to perform a storage procedure for another database?

Question

I have a stored procedure that must be executed in any table of any database on my MS Sql server. Most combinations of EXEC and USE statements have failed. 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 

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

Answer 1

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

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

It looks like you are doing this in your proc already.

You can also EXEC save the stored procedure using the Fully Qualified name - for example.

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

Honestly, your process looks good, are you getting error messages? If so, write them. Also, make sure your user has access to another database.

Answer 2

The table name in the query you are using is incorrect, it is browsing the same database, but you need to look for another database. Thus, the query will look like this:

 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 

TargetDB will be the same as your transfer database (@Database)

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

Answer 3

In this case, I try to use 2 sp in SQL Server:

• 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 

Completed 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 ie:[dbo].[TABLENAME] set @sSql='update ' +@TableCharParam +' set '+ left(@sSql,LEN(@sSql)-1) set @EachTablecmd1='if '''''+ @TableCharParam +'''''=''''[' +@TableSchema +'].[' +@TableName +']'''' ' +@sSql --ie: 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