How to execute store procedure for another DB?

Question

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

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

score 23 · Answer 1 · answered Nov 21 '11 at 05:24

23

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.

answered Nov 21 '11 at 05:24

Code Magician

23,217
7
60
77

3

`EXEC` also supports `AT` for running commands against linked servers. – ta.speot.is Nov 21 '11 at 05:27
1

and 4-part naming e.g. exec [linkedserver].[database].[schema].[proc] – Code Magician Nov 21 '11 at 05:29

score 4 · Answer 2 · answered Nov 21 '11 at 06:57

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)

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

Ricardo Figueiredo · Answer 3 · 2015-03-30T16:20:33.203

In this case I sugest to use the 2 sp's 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

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

How to execute store procedure for another DB?

3 Answers3

Linked